rich wrote:
Ross Johnson wrote:
rich wrote:
Hi,
Trying to work out how to get the Calc to check if the value of B1
is in an column C1 to C3000, then return that value in A1.
I have tried to use MATCH but when i put the equation into the rows
below, it incriments the cells so the formula for row 2 would be
=MATCH(B2,C2:C3001)
I need the formula to be
=MATCH(B2,C1:C3000)
Use the $ prefix to prevent the row index and/or column index from
changing relative to the formula's cell. In your case, you can use:
=MATCH(B2,C$1:C$3000)
Or better still, give the match range a name and use the name in the
formula.
Ross
Cheers, that got it working, but now I need to be able to return the
matched string rather than a number, any ideas on that?
Ah yeah - MATCH() returns an index value. This should work I think:
=INDEX(C$1:C$3000;MATCH(B2,C$1:C$3000);1)
Have you specifically chosen to use the default "type" parameter value
for the MATCH() function? You have not specified a "type", so it will
assume the values in the range are sorted in ascending order. If you are
looking only for exact matches in an unsorted list, as opposed to first
match equal or less than, then "type" should be "0". I.e.
=INDEX(C$1:C$3000;MATCH(B2,C$1:C$3000;0);1)
If you're happy with the default "type" value in MATCH(), then I think
you could use VLOOKUP() instead:
=VLOOKUP(B2;C$1:C$3000;C$1:C$3000)
Ross
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
