Using vlookup with two lookup values

[Tommy]

I have a range, E5:J748. I want to use a vlookup that will find the
first instance of the value in cell W5 (which is '06G271') and the
value in cell H5 (which is 'Steady State') occuring in the same row
together within the range E5:J748. So it looks for the two values in
W5 and H5, searches for the first row within E5:J748 that they occur
together and then returns the value in cell J5 as the lookup which in
this case is 'BRC'. I have no idea how to use a vlookup, or modify a
vlookup to accept two lookup values as criteria for the lookup. Any
ideas how?

Thanks,

Tom

 

[Bob Phillips]

I was going to say

=INDEX(J5:J748,MATCH(1,(E5:E748-W5)*(F5:F748=H5),0))

as an array formula, but I am wondering if you meant contiguous cells
anywhere within the row?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

=INDEX(J5:J748,MATCH(1,(E5:E748="06G271")*(H5:H748="Steady State"),0))

Enter withe Ctrl+Shift+Enter

Better to put the values in cells:

=INDEX(J5:J748,MATCH(1,(E5:E748=X1)*(H5:H748=X2),0))

X1="06G271"
X2="Steady State"

HTH

 

[Guest]

Bob,
I "assumed" the W5 was a typo and should have been E5 and so the
Op was comparing columns E & H to find J.

Interesting how we interpret these questions!

 

[Bob Phillips]

I thought that at one stage John, but then the answer can only be J5, so why
bother with a formula?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Another assumption: he used row 5 for illustration but not for the first
time, I could be wrong! My E not W assumption was simply because they are
next to each other