Index/Match to find a range of values within another range

[Motox]

Hi,
I am trying to force myself to learn to use Index/Match rather than VLookup all the time.

The problem is that 99.99% of the time that I use VLookup it is to find a range of values that match another range of values (see attached example spreadsheet), but all the examples I can find of Index/Match just match a single value (usually the contents of a single cell) within a range of values.

If I have a 2 column table that I have named “Parts” that contains a column for Part Number and another column for Cost I can easily use VLookup to populate a Cost column in another table where the Part Numbers are in a different order, for example, using the formula:

=VLOOKUP(A2,Parts,2,FALSE)

What would my Index/Match formula be to do the same thing (again, please see the example file)?

Thanks very much

Dave

[humdingaling]

you have made parts =Sheet1!$F$1:$G$5
which is a table of 2 columns
so when you try do

Please Login or Register  to view this content.

match does not know which column to look at

a form of match index formula can be

Please Login or Register  to view this content.

i found this explains it in words

Please Login or Register  to view this content.

http://www.randomwok.com/excel/how-to-use-index-match/

[Bo_Ry]

Please try at C2
=INDEX($G$2:$G$5,MATCH(A2,$F$2:$F$5,0))