Solved USING 3 VLOOKUPs with ISNA clauses

January 24, 2014 at 04:09:51
Specs: Windows 7
I have 4 fields (say, A, B, C & D)
I'd like to lookup a table using a&b&c&d if all populated, a&b&c if only 1st 3 populated, a&b if only 1st 2 populated, returning the same table column value in each case.
I'm struggling with the niceties of the problem.
Can anyone assist

See More: USING 3 VLOOKUPs with ISNA clauses

Report •

January 24, 2014 at 07:07:23
✔ Best Answer
I'm not 100% about your requirements, so let me see if I understand.

Let's say you have a table that looks like this:

       A       B        C        D          E     
1     Tom     Bill     Sue      Bob      All_Four
2     Tom     Bill     Sue               First_Three
3     Tom     Bill                       Just_Two

You want to search for Tom&Bill&Sue&Bob and if found, return All_Four.

If Tom&Bill&Sue&Bob isn't found, then search for Tom&Bill&Sue and if found, return First_Three.

If neither of those are found, then search for Tom&Bill and return Just_Two.

I assume that if none of the three combinations are found, you'll want to return a blank cell.

If I have that correct, here is how I would do it:

Insert a new Column A. In the new A1 enter this and drag it down:


This should give you:

           A            B       C          D         E          F
1     TomBillSueBob    Tom     Bill       Sue       Bob      All_Four
2     TomBillSue       Tom     Bill       Sue                First_Three
3     TomBill          Tom     Bill                          Just_Two

The new Column A is often referred to as a "helper column". You can now hide Column A if you don't want to see it.

Now use this formula to VLOOKUP Column A and return the value from Column F:


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 24, 2014 at 07:53:19
Thank you very much indeed - a quick reply and it works.

Report •

Related Solutions

Ask Question