Excel vlookup return value if two values are found in same row

Question

I have a workbook with two sheets.

I want to use a vlookup to see if two cells in a row on one sheet is present in a row on another sheet.

Sheet1:

Sheet 2:

Currently I use a VLOOKUP in sheet2 to see if the ID value is present anywhere and then if not, returns a value of "not found"

I would like to extend this VLOOKUP function, to also look at another column. It must look if ID and Favorite Color is present in the same row, and then return the error if not.

Currently function is: =IFERROR(VLOOKUP(A3,sheet1!$A$1:$E$11,1,FALSE),"NOT FOUND")

I tried to add another value to lookup like so: =IFERROR(VLOOKUP(A3**&"|"&D3**,sheet1!$A$1:$E$11,1,FALSE),"NOT FOUND")

But that didn't work.

Any ideas on how to go about doing this?

Thanks very much!

Answer 1

In order to do this with VLOOKUP, you will need to:

1. join the lookup values (first parameter)
2. join the same columns from the lookup table

You have almost got number 1 correct, but I'm not sure why you have those asterisks in there.

For number 2, you need to add a key column to the left of your table (it must be to the left) that joins ID with favorite Color on Sheet1 in the same way as you have joined it in the first parameter (i.e. pipe-separated).

For example:

When you have this, your formula will be:

=IFERROR(VLOOKUP($A3&"|"&$D3,sheet1!$A$1:$F$11,2,FALSE),"NOT FOUND")

Note that since you have added a column, your data range goes from column A to column F. I have changed the second parameter in VLOOKUP to 2 so that it returns the ID.

Answer 2

I do not have Excel 2010 so will give a solution I believe works, then one that surely works.

=VLOOKUP(A2&"|"&D2,CHOOSE({1,2},Sheet1!$A$2:$A$11&"|"&Sheet1!$D$2:$D$11,Sheet1!$A$2:$A$11),2,FALSE)

The idea here is to build a two column table IN YOUR FORMULA, not physically, then use the first for the lookup and the second for the result. Column 1 is the concatenation of the two columns, A and D and will be your lookup column. That is the Sheet1!$A$2:$A$11&"|"&Sheet1!$D$2:$D$11 portion. The second column is right after it in the formula.

You can build a table physically with a helper column. I'm a big fan of them, but then I control the spreadsheets I work on, not the other way around. This is NOT the usual situation. Rather one must work with spreadsheets others control. But a helper column can be on some entirely other page from where it is used. It doesn't have to go next to something existing, pushing things here and there. In your case, you could add a whole page just to have this helper column and a copy of the pristine ID column and so have an entire helper table somewhere that offends no one. But...

The above creates the dual column you need and collects the ID column into things. Except... it doesn't, not all by itself. To make them into a true range (which even INDIRECT() won't do under the circumstances) you use the very odd fellow called CHOOSE().

Apparently MS did not take the usual lazy shortcuts, the "who would ever need a string more than 9 characters long" limitations, the "do the first thing that occurs to you, and never think for a second past that" programming we are all familiar with. CHOOSE() works quite widely, with few limitations, and only a few odd effects. (Those odd effects don't matter here.) So you can use it to choose columns you want, and by using the "array constant" style of listing them, put them in ANY order you wish, and like with INDEX(), you can even have them occur more than once if you like.

In your case, nothing complicated is needed. You are building one column in the formula and then simply referring to the second's existing range. So the simple {1,2} works very nicely. But if you had the normal thing one encounters, needing to "look left" you COULD use CHOOSE() to simply change the order the two columns are dealt with and instantly you have a simple "look right" VLOOKUP().

So, the first part of VLOOKUP() is the A2&"|"&D2 in the formula. Next is the lookup range and CHOOSE() handles that. "2" makes sure you return just the ID and "FALSE" makes sure you get only exact matches.

Note that since your desired return is part of the two-column lookup value, you could just return that and use LEFT() with a FIND()-1 for length ("finding" the "|").

So anyhow, now just copy the formula down the table in column F and you have your results. You can pretty it up if you like for the things that do not match (it will return the #N/A error for them, so a simple IFERROR()...).

You could probably use it as an array function if desired. Use the {CSE} approach for that.

Since I've copied and pasted down the column, the above works for me. If for whatever reason it does not for you, it would probably have to do with building your two-column concatenation. The following should work for that no matter what. (Sigh... to paraphrase John Ringo though, "for certain values of 'should'..." of course. This is STILL Excel...)

=VLOOKUP(A2&"|"&D2,CHOOSE({1,2},INDEX(Sheet1!$A$2:$E$11,ROW(1:10),1)&"|"&INDEX(Sheet1!$A$2:$E$11,ROW(1:10),4),Sheet1!$A$2:$A$11),2,FALSE)

It uses INDEX() to build the concatenation, in case the direct addressing fails to do the work in Excel 2010. INDEX() is one of those functions that never fails to do what you think it should... so long as you are careful about what you think it should do. But really, it does not usually shoot off quirks in array/non-array situations so it is a pretty rock-solid choice if it seems it could do a thing. Otherwise, the approach is all the same.