Match Lookup to another worksheet

HNarli

New Member
Joined
Jun 26, 2018
Messages
24
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 :)
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

michaelm0162

New Member
Joined
Oct 21, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
=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

New Member
Joined
Oct 21, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Maybe something like
Excel Formula:
=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

New Member
Joined
Jun 26, 2018
Messages
24

ADVERTISEMENT

Maybe something like
Excel Formula:
=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

New Member
Joined
Jun 26, 2018
Messages
24
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 26, 2018
Messages
24
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Maybe
Excel Formula:
=IF(D9>=1,IFNA(INDEX(Tbl_xx_Costs.xlsx!Table1[Product_Description],MATCH(D9,Tbl_xx_Costs.xlsx!Table1[Part_Number],0)),"Empty"),"")
 

HNarli

New Member
Joined
Jun 26, 2018
Messages
24
Maybe
Excel Formula:
=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
 

Attachments

  • SO_CLOSE.JPG
    SO_CLOSE.JPG
    58.2 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
1,123,242
Messages
5,600,506
Members
414,385
Latest member
Lioness227

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top