Best formula? VLOOKUP, Index Match?

[Ian1976]

Hi,

Can someone help me I have 2 sheets (Sheet 1, Sheet 2) On Sheet 1 I have a list of numbers in Column A (A8:A47) that are also on sheet 2 column A (A2:A93), where they match up I would like to return the value from Sheet 2 which is in column F and put it in Sheet 1 Column F!

Whats the best way to do this please?

Thanks!

 

[Trebor76]

Hi Ian,

As your data goes left to right either way will suffice. The good thing about combining the two functions INDEX and MATCH is that it they can do both right and left look ups where VLOOKUP can only do right lookups. The slight trade off that I've found is that some people find the syntax of INDEX and MATCH a little harder to understand.

Here's both for you to decide (if there's no match a zero is returned):

=IFERROR(VLOOKUP(A8,Sheet2!A:F,6,FALSE),0)
=IFERROR(INDEX(Sheet2!F:F,MATCH(A8,Sheet2!A:A,0)),0)

Just put the selected formula into cell F8 of Sheet1 and copy down as needed.

Regards,

Robert

 

[Ian1976]

Thanks Robert,

I came up with this

=VLOOKUP("Catalogue",'Allocation Data'!A2:$P$93,6,FALSE)

Any idea what the index match would be?

Thanks
Ian

 

[Aladin Akyurek]

Thanks Robert,

I came up with this

=VLOOKUP("Catalogue",'Allocation Data'!A2:$P$93,6,FALSE)

Any idea what the index match would be?

Thanks
Ian

This...

=VLOOKUP("Catalogue",'Allocation Data'!$A$2:$P$93,6,FALSE)​

translates into the index/match language as follows:

=INDEX('Allocation Data'!$F$2:$F$93,MATCH("Catalogue",'Allocation Data'!$A$2:$A$93,0))

 

[Ian1976]

Hi that worked a treat is there any chance you could translate this into a match for me too, please? I am trying to return the data which is in the next column to this result! So i'm assuming it needs a +1,1 on the end? Thanks

=VLOOKUP($F$6,$F$10:$NK$13,MATCH($D$6,$F$9:$NK$9,0),FALSE)

 

[Aladin Akyurek]

Hi that worked a treat is there any chance you could translate this into a match for me too, please? I am trying to return the data which is in the next column to this result! So i'm assuming it needs a +1,1 on the end? Thanks

=VLOOKUP($F$6,$F$10:$NK$13,MATCH($D$6,$F$9:$NK$9,0),FALSE)

Looks like:

=VLOOKUP($F$6,$F$10:$NK$13,MATCH($D$6,$F$9:$NK$9,0))

What do you have F6? And what do you have in D6?