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

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, returnAll_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&E1

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_TwoThe 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))

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

Thank you very much indeed - a quick reply and it works.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History