How to lookup a value in excel?

[Guest]

Does anyone know how to lookup a value in following example?

[A1] 1 [B1] 45
[A2] 2 [B2] 90
[A3] 3 [B3] 88
[A4] 4 [B4] 56

I would like to look up the highest value under B colume, such as 90 in cell
B2 in this case, and return the value of 2 from cell A2 into C1 cell.
Does anyone have any suggestion?
Thank you very much
Eric

 

[Guest]

Either use VLOOKUP() or

=INDEX(A:A,MATCH(MAX(B:B),B:B,0),0)

 

[Trevor Shuttleworth]

Eric

one way:

=INDEX(MATCH(MAX(B:B),B:B,0),A:A)

Regards

Trevor

 

[Guest]

Thank everyone for suggestion

=INDEX(MATCH(MAX(B:B),B:B,0),A:A)
If I input this formula in cell C1, then it works fine, but if I input this
formula in cell C2, then it returns #REF!, could you please tell me how to
fix this value?
Thank everyone very much
Eric

 

[JMay]

Here's another way (close to TS's - FYI):

=OFFSET(A1,MATCH(MAX(B:B),B:B,0)-1,0)

Eric

one way:

=INDEX(MATCH(MAX(B:B),B:B,0),A:A)

Regards

Trevor

Does anyone know how to lookup a value in following example?

[A1] 1 [B1] 45
[A2] 2 [B2] 90
[A3] 3 [B3] 88
[A4] 4 [B4] 56

I would like to look up the highest value under B colume, such as 90 in
cell
B2 in this case, and return the value of 2 from cell A2 into C1 cell.
Does anyone have any suggestion?
Thank you very much
Eric

 

[Guest]

Thank everyone very much
Eric

Here's another way (close to TS's - FYI):

=OFFSET(A1,MATCH(MAX(B:B),B:B,0)-1,0)

Eric

one way:

=INDEX(MATCH(MAX(B:B),B:B,0),A:A)

Regards

Trevor

Does anyone know how to lookup a value in following example?

[A1] 1 [B1] 45
[A2] 2 [B2] 90
[A3] 3 [B3] 88
[A4] 4 [B4] 56

I would like to look up the highest value under B colume, such as 90 in
cell
B2 in this case, and return the value of 2 from cell A2 into C1 cell.
Does anyone have any suggestion?
Thank you very much
Eric

 

[JMay]

I see what you mean and I too don't understand why the last parameter
(the A:A) gets messed up evaluating to #NUM! - Using my offset function
(modified
Below changing the A1 ref to $A$1) it seems to work wherever it's moved.

=OFFSET($A$1,MATCH(MAX(B:B),B:B,0)-1,0)

I'm interested in know why the #NUM! occurs...

Jim May

 

[JMay]

OK, found OUT something (I didn't fully understand - and still don't
totally, but) If you commit TS's Original formula with the
Ctrl+Shift+Enter keys VERSUS just eh enter key - It works WHEREEVER it
is entered or later moved Or copied. The "CSE" conerts the normal
formula into what is called
An array-formula - which is what is needed, Hope this helps,,,
Jim

 

[Trevor Shuttleworth]

Eric

actually, Gary's Student gave you the correct answer.

My answer was returning the row number, not the value ... it just so
happened that the data in the cell (in the example) matched the row number.

CSE with my formula does always work but always returns the row not the
value.

So, apologies ... not sure why the error is generated but an interesting
debate. Confusing !

Regards

Trevor

Thank everyone very much
Eric

Here's another way (close to TS's - FYI):

=OFFSET(A1,MATCH(MAX(B:B),B:B,0)-1,0)

Eric

one way:

=INDEX(MATCH(MAX(B:B),B:B,0),A:A)

Regards

Trevor


Does anyone know how to lookup a value in following example?

[A1] 1 [B1] 45
[A2] 2 [B2] 90
[A3] 3 [B3] 88
[A4] 4 [B4] 56

I would like to look up the highest value under B colume, such as 90
in
cell
B2 in this case, and return the value of 2 from cell A2 into C1 cell.
Does anyone have any suggestion?
Thank you very much
Eric