Match Lookup to another worksheet

[HNarli]

Hi,

I have the following VLOOKUP formula which is working well. =IF(D9>=1,IFERROR(VLOOKUP(D9,[Tbl_XX_Costs.xlsx]Sheet1!$B$2:$S$4999,4,FALSE),"EMPTY"),"")

However I came by an article online that explained that using the MATCH instead of Vlookups to a column is better because if the column in the table ever moved it won't break the spreadsheet. For this reason I would like to swap my formula to use the MATCH way instead.

Their example is this:

VLOOKUP EXAMPLE =VLOOKUP(B5, Table1, 2, 0)
MATCH EXAMPLE =VLOOKUP(B5, Table1, MATCH(C4,Table1[#Headers],0), 0)

I've tried myself to adapt my version to match lookup to table1 to the column header 'Part' but no matter what I try it doesn't work. I think it's because my version links to a separate workbook. Can anyone help me adapt my version to work with the match example above. I hope that makes sense!

MY VERSION =IF(D9>=1,IFERROR(VLOOKUP(D9,[Tbl_XX_Costs.xlsx]Sheet1!$B$2:$S$4999,4,FALSE),"EMPTY"),"")

Thanks

 

[michaelm0162]

=INDEX('COLUMN YOU WANT TO LOOK UP THE VAULE' , MATCH('VALUE YOURE LOOKING UP' , 'COLUMN YOURE LOOKING THE VALUE UP IN', 0))

MY VERSION =IF(D9>=1,IFERROR(VLOOKUP(D9,[Tbl_XX_Costs.xlsx]Sheet1!$B$2:$S$4999,4,FALSE),"EMPTY"),"")
so in your case =if(D9>=1,iferror(index($E:$E,match(D9,$B:$B,0)),"Empty"),"")

 

[michaelm0162]

sorry the first value.. is the column you want the data returned from... i miss typed and i don't know how to edit on this forum

=INDEX('COLUMN YOU WANT TO RETURN THE VAULE' , MATCH('VALUE YOURE LOOKING UP' , 'COLUMN YOURE LOOKING THE VALUE UP IN', 0))

 

[Fluff]

Maybe something like

=IF(D9>=1,IFNA(INDEX(Tbl_xx_Costs.xlsx!Table1[#Data],MATCH(D9,Tbl_xx_Costs.xlsx!Table1[????],0),MATCH(B9,Tbl_xx_Costs.xlsx!Table1[#Headers],0)),"Empty"),"")

Where D9 is the value to lookup, B9 is the name of the column header you want to return & ??? is the column header of your lookup.

 

[HNarli]

Maybe something like

=IF(D9>=1,IFNA(INDEX(Tbl_xx_Costs.xlsx!Table1[#Data],MATCH(D9,Tbl_xx_Costs.xlsx!Table1[????],0),MATCH(B9,Tbl_xx_Costs.xlsx!Table1[#Headers],0)),"Empty"),"")

Where D9 is the value to lookup, B9 is the name of the column header you want to return & ??? is the column header of your lookup.

Thank you for trying but it doesn't work. My table was table 2 and the table header was Product_Description
I wasn't sure what you wrere refering to when you say 'B9 is the name of the column header you want to return'

 

[HNarli]

sorry the first value.. is the column you want the data returned from... i miss typed and i don't know how to edit on this forum

=INDEX('COLUMN YOU WANT TO RETURN THE VAULE' , MATCH('VALUE YOURE LOOKING UP' , 'COLUMN YOURE LOOKING THE VALUE UP IN', 0))

Thanks for trying but I'm not that advanced! I get what you are saying but it's too complex for me when looking up to another sheet, I can't get your example to work as your example doesn't show how to reference another sheet. Thanks anyway

 

[Fluff]

What is the name of the column in Table2 that contains the value you want to find?
And what is the name of the column in table2 that contains the value the formula should return?

 

[HNarli]

What is the name of the column in Table2 that contains the value you want to find?
And what is the name of the column in table2 that contains the value the formula should return?

I would like to find the Product_Description based on the Part_Number

 

[Fluff]

Maybe

=IF(D9>=1,IFNA(INDEX(Tbl_xx_Costs.xlsx!Table1[Product_Description],MATCH(D9,Tbl_xx_Costs.xlsx!Table1[Part_Number],0)),"Empty"),"")

 

[HNarli]

Maybe

=IF(D9>=1,IFNA(INDEX(Tbl_xx_Costs.xlsx!Table1[Product_Description],MATCH(D9,Tbl_xx_Costs.xlsx!Table1[Part_Number],0)),"Empty"),"")

Amazingly close thanks! So the only thing is now if it can't find a part number it breaks the sums, I thought it was something to do with "empty" and tried to put '0' here instead but it doesn't work. See the yellow parts, I keep getting #VALUE