Multiple Identifier VLOOKUP or other recommendation

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
Greetings,
Thank you in advance for helping if you can. I am using a PC, with excel 2013. I would like to use VLOOKUP with two unique identifiers, or if this should be done using INDEX/MATCH etc. please let me know as I have tried and haven't succeeded. I have played with concatenating and such and am not having any luck because of the formatting. In one sheet, I have a column of site locations and a row of species names. In another sheet, I have three columns, a column of site locations, a column of species names for those site locations, and a cover code. In the first sheet I need it to lookup both the site location in A2 and the species name in B1 to return the cover code from the match in Column A & B in dataset of $A$2:$C:$172, and return the data from the third column. I have included a link to the dataset. I have the data in Sheet 1 where I need the VLOOKUP and Sheet 2 with the cover code I need. To the right of the data in sheet 1 is a copy of the data in sheet 2 in case it makes it easier to visualize everything. This is just a portion of the dataset, or I would just do it, but I will have a substantial number of rows in the end to deal with. Any help or advice would be greatly appreciated.
https://app.box.com/s/l2gvs42l9ub3t8hc4txmkwyzklp3n60u
Thank you again for your time and help,
Maggie:eek:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Insert the following in B2:

Code:
=IFERROR(LOOKUP(2,1/((Sheet2!$B$2:$B$172=Sheet1![COLOR=#FF0000]A2[/COLOR])*(Sheet2!$C$2:$C$172=Sheet1![COLOR=#FF0000]$B$1[/COLOR])),Sheet2!$D$2:$D$172),"")

Autofill down. Then change the two highlighted items from A2 and $B$1 to $A$2 and B1 etc etc. AutoFill to the right.
 
Upvote 0
Oh my word in heaven! Thank you so incredibly much for your help. I had looked at formulas like this for the issue, and even tried, but I did not get it right. Now that I see the formula set up for my circumstances it enables me to understand the language of it better. You are so wonderful to help. It worked great! I will be using this in modified form for many other projects.
Thanks again and Best Wishes,
Maggie
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top