Lookup Table with Wild Cards

[AlexGuest]

Hello. I am trying to create a formula that will lookup a value from a table based on 2 criteria, but in certain cases I only want 1 criteria. The table would like this...

In cells A1:A5
Blue
Red
Green
*
Black

In cells B1:B5
Dog
Fish
Fish
Fish
Dog

In cells C1:C5
Result1
Result2
Result3
Result4
Result5

If the lookup values were Red and Fish, I want the formula result to be Result2. Green Fish = Result3. Any other color Fish should be Result4.

I couldn't use SUMPRODUCT since I'm looking up text and an Index Match array doesn't recognize the *. So I came up with the following...

=LOOKUP(10^10,SEARCH(A1:A5,A7)*SEARCH(B1:B5,B7),C1:C5)
Cell A7 is the lookup value for column A
Cell B7 is the lookup value for column B

This almost works perfectly except that Red Fish returns Result4 instead of Result2 because the lookup function returns the last match. So, my questions...
1) Is there a way to modify my formula so that it returns the first match?
2) Is there a another formula that would work or a way to do this without the * wildcard?

If not, I think I'll have to invert my table which i'm hoping to avoid.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td] Blue[/td][td] Dog[/td][td] Result1[/td][td][/td][td] red[/td][td] fish[/td][td]Result2[/td][/tr]
[tr][td]

2​

[/td][td] Red[/td][td] Fish[/td][td] Result2[/td][td][/td][td] green[/td][td] fish[/td][td]Result3[/td][/tr]
[tr][td]

3​

[/td][td] Green[/td][td] Fish[/td][td] Result3[/td][td][/td][td] *[/td][td] fish[/td][td]Result4[/td][/tr]
[tr][td]

4​

[/td][td] *[/td][td] Fish[/td][td] Result4[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td] Black[/td][td] Dog[/td][td] Result5[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In G1 control+shift+enter, not just enter, and copy down:

=INDEX($C$1:$C$5,MATCH("~"&E1,IF($B$1:$B$5=F1,$A$1:$A$5),0))

Note. If you ever need a large constant as a look up value in the last value formulas, use a constant of Excel itself: 9.99999999999999E+307.

 

[AlexGuest]

Thank you for the quick response Aladin. Unfortunately, the index-match-array approach does not seem to work with an * in the table. I apologize if my post wasn't clear. You have an * as a lookup value (cell E3 in your example). What I am looking for is a formula that will return Result4 if Orange is in E3 in your example. Basically, a red fish is Result2, a green fish is Result3 and any other fish is Result4.

If my formula can be modified to return the first match instead of the last match, and there is another way that doesn't use the * in the table, please let me know.

 

[Aladin Akyurek]

Thank you for the quick response Aladin. Unfortunately, the index-match-array approach does not seem to work with an * in the table. I apologize if my post wasn't clear. You have an * as a lookup value (cell E3 in your example). What I am looking for is a formula that will return Result4 if Orange is in E3 in your example. Basically, a red fish is Result2, a green fish is Result3 and any other fish is Result4.

If my formula can be modified to return the first match instead of the last match, and there is another way that doesn't use the * in the table, please let me know.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td] Blue[/td][td] Dog[/td][td] Result1[/td][td][/td][td] red[/td][td] fish[/td][td]Result2[/td][/tr]
[tr][td]

2​

[/td][td] Red[/td][td] Fish[/td][td] Result2[/td][td][/td][td] green[/td][td] fish[/td][td]Result3[/td][/tr]
[tr][td]

3​

[/td][td] Green[/td][td] Fish[/td][td] Result3[/td][td][/td][td] orange[/td][td] fish[/td][td]Result4[/td][/tr]
[tr][td]

4​

[/td][td] *[/td][td] Fish[/td][td] Result4[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td] Black[/td][td] Dog[/td][td] Result5[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In G1 control+shift+enter, not just enter, and copy down:

=INDEX($C$1:$C$5,MATCH(IF(ISNA(MATCH(E1,$A$1:$A$5,0)),"~*",E1),
    IF($B$1:$B$5=F1,$A$1:$A$5),0))

 

[AlexGuest]

That works perfectly, but could you please help me make the formula more dynamic so I can...
- Have an * in column B (in addition to an * in column A of another row). In your example, if B5 is changed to an *, I'd like Black Goat to return Result5.
- Expand from this 2 criteria column example to more criteria columns (each column allowing *'s).

I was hoping to be able to figure out how to expand your formula on my own, but wasn't able to figure it out. If you could provide an explanation on how your formula works, that would be helpful too.

Thanks again Aladin

 

[Aladin Akyurek]

That works perfectly, but could you please help me make the formula more dynamic so I can...
- Have an * in column B (in addition to an * in column A of another row). In your example, if B5 is changed to an *, I'd like Black Goat to return Result5.
- Expand from this 2 criteria column example to more criteria columns (each column allowing *'s).

I was hoping to be able to figure out how to expand your formula on my own, but wasn't able to figure it out. If you could provide an explanation on how your formula works, that would be helpful too.

Thanks again Aladin

In G1 control+shift+enter and copy down:

=INDEX($C$1:$C$5,MATCH(IF(ISNA(MATCH(E1,$A$1:$A$5,0)),"~*",E1),
    IF($B$1:$B$5=IF(ISNA(MATCH(F1,$B$1:$B$5,0)),"*",F1),$A$1:$A$5),0))

Try to apply F9 to the selected bits of the formula on the formula bar in order to see to what those bits evaluate to. This might help you to arrive at an understanding how it works.

 

[AlexGuest]

That works for 2 criteria columns. I've been staring at this for an hour trying to figure out how to expand it to a 4 criteria column example. I haven't been able to figure it out. How can the formula be changed so that a 4 criteria table can be used. See example...

[TABLE="width: 768"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Row\Col[/TD]
[TD="class: xl69, width: 64"] A[/TD]
[TD="class: xl69, width: 64"] B[/TD]
[TD="class: xl69, width: 64"] C[/TD]
[TD="class: xl69, width: 64"] D[/TD]
[TD="class: xl69, width: 64"] E[/TD]
[TD="class: xl69, width: 64"] F[/TD]
[TD="class: xl69, width: 64"] G[/TD]
[TD="class: xl69, width: 64"] H[/TD]
[TD="class: xl69, width: 64"] I[/TD]
[TD="class: xl69, width: 64"]J[/TD]
[TD="class: xl69, width: 64"] K[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64"] 1[/TD]
[TD="class: xl70, width: 64"]Blue[/TD]
[TD="class: xl70, width: 64"]Dog[/TD]
[TD="class: xl70, width: 64"]Apple[/TD]
[TD="class: xl70, width: 64"]Chicago[/TD]
[TD="class: xl70, width: 64"]Result1[/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl70, width: 64"]Red[/TD]
[TD="class: xl70, width: 64"]fish[/TD]
[TD="class: xl70, width: 64"]Bacon[/TD]
[TD="class: xl70, width: 64"]NY[/TD]
[TD="class: xl68, width: 64"]Result2[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64"] 2[/TD]
[TD="class: xl70, width: 64"]Red[/TD]
[TD="class: xl70, width: 64"]Fish[/TD]
[TD="class: xl70, width: 64"]*[/TD]
[TD="class: xl70, width: 64"]NY[/TD]
[TD="class: xl70, width: 64"]Result2[/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl70, width: 64"]Green[/TD]
[TD="class: xl70, width: 64"]fish[/TD]
[TD="class: xl70, width: 64"]Sausage[/TD]
[TD="class: xl70, width: 64"]Chicago[/TD]
[TD="class: xl68, width: 64"]Result4[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64"] 3[/TD]
[TD="class: xl70, width: 64"]Green[/TD]
[TD="class: xl70, width: 64"]Fish[/TD]
[TD="class: xl70, width: 64"]Pear[/TD]
[TD="class: xl70, width: 64"]*[/TD]
[TD="class: xl70, width: 64"]Result3[/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl70, width: 64"]Black[/TD]
[TD="class: xl70, width: 64"]Dog[/TD]
[TD="class: xl70, width: 64"]Bacon[/TD]
[TD="class: xl70, width: 64"]Chicago[/TD]
[TD="class: xl68, width: 64"]Result5[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64"] 4[/TD]
[TD="class: xl70, width: 64"]*[/TD]
[TD="class: xl70, width: 64"]Fish[/TD]
[TD="class: xl70, width: 64"]*[/TD]
[TD="class: xl70, width: 64"]*[/TD]
[TD="class: xl70, width: 64"]Result4[/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl68, width: 64"]Green[/TD]
[TD="class: xl68, width: 64"]Fish[/TD]
[TD="class: xl68, width: 64"]Pear[/TD]
[TD="class: xl68, width: 64"]Paris[/TD]
[TD="class: xl68, width: 64"]Result3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] 5[/TD]
[TD="class: xl66, width: 64"]Black[/TD]
[TD="class: xl66, width: 64"]*[/TD]
[TD="class: xl66, width: 64"]*[/TD]
[TD="class: xl66, width: 64"]Chicago[/TD]
[TD="class: xl66, width: 64"]Result5[/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"]Black[/TD]
[TD="class: xl67, width: 64"]Cat[/TD]
[TD="class: xl67, width: 64"]Meat[/TD]
[TD="class: xl67, width: 64"]Chicago[/TD]
[TD="class: xl67, width: 64"]Result5[/TD]
[/TR]
</tbody>[/TABLE]

Similar to the previous examples, columns A to D are the criteria columns, column E is the desired result. Columns G to J are lookup values and column K is where I need a formula that returns the applicable result from column D?

I'm open to using a different approach if anyone has one but I need to have a table of rules similar to this that I will update frequently.

 

[Aladin Akyurek]

We are raising the stakes, aren't we?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

M​

[/td][/tr][tr][td]

1​

[/td][td] Blue[/td][td] Dog[/td][td] Apple[/td][td] Chicago[/td][td] Result1[/td][td] [/td][td] Red[/td][td] fish[/td][td] Bacon[/td][td] NY[/td][td] Result2[/td][/tr]
[tr][td]

2​

[/td][td] Red[/td][td] Fish[/td][td] *[/td][td] NY[/td][td] Result2[/td][td] [/td][td] Green[/td][td] fish[/td][td] Sausage[/td][td] Chicago[/td][td] #N/A[/td][/tr]
[tr][td]

3​

[/td][td] Green[/td][td] Fish[/td][td] Pear[/td][td] *[/td][td] Result3[/td][td] [/td][td] Black[/td][td] Dog[/td][td] Bacon[/td][td] Chicago[/td][td] #N/A[/td][/tr]
[tr][td]

4​

[/td][td] *[/td][td] Fish[/td][td] *[/td][td] *[/td][td] Result4[/td][td] [/td][td] Green[/td][td] Fish[/td][td] Pear[/td][td] Paris[/td][td] Result3[/td][/tr]
[tr][td]

5​

[/td][td] Black[/td][td] *[/td][td] *[/td][td] Chicago[/td][td] Result5[/td][td] [/td][td] Black[/td][td] Cat[/td][td] Meat[/td][td] Chicago[/td][td] Result5[/td][/tr]
[/table]

In M2 control+shift+enter, not just enter, and copy down:

=INDEX($F$1:$F$5,MATCH(1,IF($A$1:$A$5=IF(ISNA(MATCH(H1,$A$1:$A$5,0)),"*",H1),
    IF($B$1:$B$5=IF(ISNA(MATCH(I1,$B$1:$B$5,0)),"*",I1),
    IF($C$1:$C$5=IF(ISNA(MATCH(J1,$C$1:$C$5,0)),"*",J1),
    IF($D$1:$D$5=IF(ISNA(MATCH(K1,$D$1:$D$5,0)),"*",K1),1)))),0))

If these results are not admissible, consider the following set up...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr][tr][td]

1​

[/td][td] *[/td][td] Fish[/td][td] *[/td][td] *[/td][td] *|Fish|*|*[/td][td] Result4[/td][td] Green[/td][td] Fish[/td][td] Pear[/td][td] Paris[/td][td] Green|Fish|Pear|Paris[/td][/tr]
[tr][td]

2​

[/td][td] Black[/td][td] *[/td][td] *[/td][td] Chicago[/td][td] Black|*|*|Chicago[/td][td] Result5[/td][td] Black[/td][td] Cat[/td][td] Meat[/td][td] Chicago[/td][td] Black|Cat|Meat|Chicago[/td][/tr]
[tr][td]

3​

[/td][td] Blue[/td][td] Dog[/td][td] Apple[/td][td] Chicago[/td][td] Blue|Dog|Apple|Chicago[/td][td] Result1[/td][td] Red[/td][td] fish[/td][td] Bacon[/td][td] NY[/td][td] Red|fish|Bacon|NY[/td][/tr]
[tr][td]

4​

[/td][td] Green[/td][td] Fish[/td][td] Pear[/td][td] *[/td][td] Green|Fish|Pear|*[/td][td] Result3[/td][td] Black[/td][td] Dog[/td][td] Bacon[/td][td] Chicago[/td][td] Black|Dog|Bacon|Chicago[/td][/tr]
[tr][td]

5​

[/td][td] Red[/td][td] Fish[/td][td] *[/td][td] NY[/td][td] Red|Fish|*|NY[/td][td] Result2[/td][td] Green[/td][td] fish[/td][td] Sausage[/td][td] Chicago[/td][td] Green|fish|Sausage|Chicago[/td][/tr]
[/table]

In E1 just enter and copy down:

=A1&"|"&B1&"|"&C1&"|"&D1

In L1 just enter and copy down:

=H1&"|"&I1&"|"&J1&"|"&K1

Now sort A:L on E in ascending order...

In M1 just enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH($E$1:$E$5,L1),$F$1:$F$5)

If done properly, the results in M would look like below:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

M​

[/td][/tr][tr][td]

1​

[/td][td] Result3[/td][/tr]
[tr][td]

2​

[/td][td] Result5[/td][/tr]
[tr][td]

3​

[/td][td] Result2[/td][/tr]
[tr][td]

4​

[/td][td] Result5[/td][/tr]
[tr][td]

5​

[/td][td] Result4[/td][/tr]
[/table]

 

[AlexGuest]

The first option doesn't help due to the #N/A's. A couple questions on the second option...
1) Why is sorting necessary? Doesn't the lookup formula find the first match starting from the last row of the table searching backwards/up?
2) Is there a way to modify the formula so that it finds the first match starting from the first row of the table searching down?

 

[Aladin Akyurek]

The first option doesn't help due to the #N/A's.

Delivering a blank instead #N/A there is no problem. The question is whether such is admissible.

A couple questions on the second option...
1) Why is sorting necessary? Doesn't the lookup formula find the first match starting from the last row of the table searching backwards/up?

We don't want the set up stop too early while trying to match.

2) Is there a way to modify the formula so that it finds the first match starting from the first row of the table searching down?

It's a last match construct.