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 manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History