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

#1
January 24, 2014 at 07:07:23
 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:=B1&C1&D1&E1This 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:=IF(ISNA(VLOOKUP((B1&C1&D1&E1),\$A\$1:\$F\$3,6,0)),IF(ISNA(VLOOKUP((B1&C1&D1),\$A\$1:\$F\$3,6,0)),IF(ISNA(VLOOKUP((B1&C1),\$A\$1:\$F\$3,6,0)),"",VLOOKUP((B1&C1),\$A\$1:\$F\$3,6,0)),VLOOKUP((B1&C1&D1),\$A\$1:\$F\$3,6,0)),VLOOKUP((B1&C1&D1&E1),\$A\$1:\$F\$3,6,0))