Vlook up not finding what I am looking for

[OHS Morgan]

Hi

I am struggling with a simple formula - I am not an expert but do like working with Excel but I have come across a look-up problem when looking for the stock number of the item and can not work out what I am doing wrong. If you get a chance can you have a look and let me know what is the problem?

Cheers
Morgan

[The_Snook]

Hi Morgan,

In a VLOOKUP formula, the first column of the 'table array' range needs to contain the 'lookup value' you are searching for (if that makes sense?). I've amended the attached.

Regards,

Snook

[OHS Morgan]

Hi The_Snook

Thank you for your reply - I am looking for the stock number from the lookup - this looks like it has returned the price rather than the stock number in the stock no column. I just can't seem to work it out.

Cheers
Morgan

[The_Snook]

Ah my bad, try this in cell B8:

=INDEX(B2:B5,MATCH(C8,C2:C5,0))

You can't use a VLOOKUP because the value you want to return is to the left of the criteria you are looking up.

[The_Snook]

Or:

=INDEX(Sheet1!B2:B6,MATCH(C8,Sheet1!C2:C6,0))

If you want to look up the stock reference from the data on Sheet1.

[OHS Morgan]

Hi The_Snook

Thank you so much - Vlookup will only look to the right for a solution, I was unaware of that. I really appreciate your help. I have used the 2nd Index formula you sent which has worked a treat.

That formula has 2 criteria in as much as index (look at the table the information is in) Then a match function to find the item in question. With Excel, I know enough to get myself in trouble often but mostly seem to be able to work it out but this time I felt sure Vlookup was the way to go.

Thanks again and have a great day or evening
Cheers
Morgan

[TMS]

With 365, you could use this:

Please Login or Register  to view this content.

[TMS]

Or you could use XLOOKUP:

Please Login or Register  to view this content.

[TMS]

Thanks for the rep.