Using VLOOKUP to search two coloumns but return one value PLEASE HELP!!

ThereIsNoSpoon

New Member
Joined
May 23, 2014
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hello all,

I would greatly appreciate help from somebody regarding this problem I currently have. It is frazzling me.

on the commercial invoice sheet that I had created, I have a slot for a commodity code to be automatically filled using a VLOOKUP formula. This is powered by a drop-down menu that is populated by all of our asset numbers.
as it stands, the form works well.

BUT...

It only looks up the Export code on our asset database.
To get around this I had made two sheets for import and export. I had hoped to be able to combine them so that I only have the one form to maintain.
So I would need a formula that looks at the asset number and the import/ export drop-down boxes for the search criteria. This would still look at the same asset database but search both the import and export columns (which are next to each other on the same table) to return the value that matched.

I'm not even sure that this can be done but any advice on this would really put me out of my misery.

The formula that I am using to return a value is:

=IFERROR(IF(VLOOKUP($K14,tbl_TJL_Data,9,FALSE)=0,"",VLOOKUP($K14,tbl_TJL_Data,9,FALSE)),"")

The IFERROR and IF functions are there to return a blank if the sheet has no data on it or if the asset has a blank in its entry, zeroes, etc. ( i am sure that it looks clunky, there may be a better way to present that too...)

K14 is the drop-down for the asset.

tbl_TJL_Data is the asset table name.

Column 9 is for the Export code. so column 10 is for Import codes.

My Import/ Export drop-down lives on cell N13.

I hope that I have explained all this clearly enough because reading it over seems like a mess.

Many thanks

Rob
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,019
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use.

Also are the import/export codes text or numbers?
 

ThereIsNoSpoon

New Member
Joined
May 23, 2014
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Apologies, I am on 2010 and an example of the code is 84.25.42.00
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,019
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
=IFERROR(T(VLOOKUP($K14,tbl_TJL_Data,IF(N13="Import",10,9),FALSE)),"")
 

ThereIsNoSpoon

New Member
Joined
May 23, 2014
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Wow! thank you so much! that has certainly eased the headache!

Works perfectly!

Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,744
Messages
5,597,880
Members
414,183
Latest member
jerung3000

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