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 :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,772
Office Version
  1. 365
Platform
  1. Windows
What is the formula that returns the error?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,122,261
Messages
5,595,133
Members
413,971
Latest member
User786

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