Using Lookup

[js2756]

I am trying to convert a grade to a numerical equivalent:

lookup(A1, {"A*","A","B"},{58,52,46}) but if cell A1 contains the A star (A*) grade, the lookup formula returns 52 instead of 58.

I think it is a problem of interpreting the asterisk as a wildcard. How can I get the formula to work as expected?

Thanks

John

 

[texasalynn]

Welcome to MrExcel board...

does this help

Excel Workbook
	A	B
1	a*	58
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=IF(A1="A",52,IF(AND(A1=LEN(A1)>1,LEFT(A1,1)="A"),58,IF(A1="B",46,"")))

 

[T. Valko]

I am trying to convert a grade to a numerical equivalent:

lookup(A1, {"A*","A","B"},{58,52,46}) but if cell A1 contains the A star (A*) grade, the lookup formula returns 52 instead of 58.

I think it is a problem of interpreting the asterisk as a wildcard. How can I get the formula to work as expected?

Thanks

John

Just swap places with the A:

=LOOKUP(A1,{"A","A*","B"},{52,58,46})

 

[Aladin Akyurek]

Try also something like:

=VLOOKUP(A1,{"A*",58;"A",52;"B",46},2,0)

 

[js2756]

Thanks guys, these all do the job but I was wondering why Biff's version works and mine doesn't?


js

 

[texasalynn]

Biff's is sorted where your's was not. Lookup needs the data to be sorted in ascending order.

 

[T. Valko]

Thanks guys, these all do the job but I was wondering why Biff's version works and mine doesn't?


js

The lookup_vector must be sorted in ascending order for the function to work properly.

To see how Excel evaluates the sort order, simply enter the values in some cells then sort them in ascending order.

A*, A, B

will get sorted to

A, A*, B

 

[js2756]

Thanks for 'sorting' me out guys!

js

 

[Aladin Akyurek]

Thanks for 'sorting' me out guys!

js

Better to avoid invoking LOOKUP here. A better alternative is VLOOKUP (or INDEX/MATCH) with the match-type set to 0.

 

[T. Valko]

Thanks for 'sorting' me out guys!

js

You're welcome. Thanks for the feedback!