Nested vlookups and Index matching for multiple conditions within a cell

Question

I have some logic that I am not able to figure out. I want a cell (E1) to return a value under certain conditions. If a number is entered into C1 lets say ‘4’, I need that cell (E1) to lookup a table that has that value.

(for example…vlookup(C1,A1:B8,1,False).

The second condition is that the same cell (E1) needs to return that value of 4 if and when a different cell (G1) is equal from any range of B1:B5.

These are two conditions. The first one must match and the second one must match from a range of cells. Do I need to use a nested vlookup, index match, or some other function to return the value 4 into E1. Thanks

on second thought i dont know if that will work. Basically, i have a dashboard with 3 images. image 1 will appear when a value matches a values in column A and when a different value (G1) matches a cell in a range. doesnt need to be approx, can be exact as well — ENGR024, Feb 08 '14 at 00:27

score 1 · Accepted Answer · answered Feb 08 '14 at 00:31

1

There are many ways to check for two conditions. Here is one way.

In E1:

=$C$1*(MATCH($C$1,$A$1:$A$8,0)>0)*(MATCH($G$1,$B$1:$B$5,0)>0)

answered Feb 08 '14 at 00:31

andy holaday

2,282
1
17
25

This looks like (or rather hints to) an interesting alternative to `LOOKUP`. Is that so? Do you think something like this can be used as another option to [this](http://stackoverflow.com/a/21582535/2707864)? – sancho.s ReinstateMonicaCellio Feb 08 '14 at 09:09
@sancho.s yes, and it looks like one of the answers to that question uses `INDEX` and `MATCH` as an alternative to `LOOKUP`. – andy holaday Feb 08 '14 at 19:27
Yes, there is one such. But I think yours is slightly different, with a product of `MATCH`es instead of `MATCH` of a product. Interesting variations. – sancho.s ReinstateMonicaCellio Feb 09 '14 at 16:31

Nested vlookups and Index matching for multiple conditions within a cell

1 Answers1