CODEWITHSUNDEEP
excelexcel-formula
Ed Fearon
Ed Fearon

Reputation: 93

Excel: Lookup concatenated value

Does anyone know why the following returns #N/A?

=LOOKUP(TEXT(CONCATENATE($I$8,$I$5,"1"),0), CritCodes, Criteria)

I5 and I8 contain the following formulae and are correctly evaluating to the number 4 and R respectively:

=LOOKUP(B37, LevelsRange,LowLevel)
=LEFT(B29,1)

So the problem formula is correctly being evaluated to this, but it won't find the result.

=LOOKUP("R41", CritCodes, Criteria)

Any help would be greatly appreciated.

Upvotes: 0

Views: 1905

Answers (2)

barry houdini
barry houdini

Reputation: 46401

As per my comment - try using this formula

=INDEX(criteria,MATCH($I$8&$I$5&1,Critcodes,0))

LOOKUP isn't normally appropriate for lookups unless the lookup range is sorted ascending.....and you might want a "nearest match". INDEX/MATCH as used here doesn't require any sorting but also only gives an exact match, so if the lookup value doesn't exist in Critcodes you get #N/A

Note that in your example if I8 = "R" and I5 = 4 then $I$8&$I$5&1 = "R41" - obviously thats a text value but that will also be the case if I8 and I5 were both numbers, so ideally Critcodes should all be text values (to do that precede any number with ' like '41)

Upvotes: 3

MattCrum
MattCrum

Reputation: 1110

It's trying to lookup the string "41" rather than the integer 41. Change the formula to this:

=LOOKUP(INT(CONCATENATE($I$5,"1")), Writing, Criteria)

Upvotes: 1

Related Questions