Using vlookup with two lookup values
 

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

Using vlookup with two lookup values
 

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)

"Tommy" wrote in message
ups.com...
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

Using vlookup with two lookup values
 

=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

"Tommy" wrote:

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

Using vlookup with two lookup values
 

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!

"Toppers" wrote:

=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

"Tommy" wrote:

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

Using vlookup with two lookup values
 

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)

"Toppers" wrote in message
...
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!

"Toppers" wrote:

=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

"Tommy" wrote:

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

Using vlookup with two lookup values
 

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
on the keyboard ... but perhaps that just reflects my own keyboard dyslexia!

"Bob Phillips" wrote:

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)

"Toppers" wrote in message
...
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!

"Toppers" wrote:

=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

"Tommy" wrote:

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