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
53,324
Office Version
  1. 365
Platform
  1. Windows
What is the formula that returns the error?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,123,427
Messages
5,601,605
Members
414,462
Latest member
StageRiis

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