CODEWITHSUNDEEP
excelvlookup
user1117617
user1117617

Reputation:

VLOOKUP on Calculated Cell Failing

I am new to using VLOOKUPS - I am having an issue where the value its pointing at (Cell E2) is a calculated cell in itself, the VLOOKUP only seems to work if I Hardcode a value into the cell.

Are there any ways I can get around this - I am surprised I couldn't find this on the Internet so far given it must be quite a common problem

VLOOKUP Code: =VLOOKUP(E2,'Colour Code'!A$2:B$11, 2, FALSE)

Formula in Cell E2: =LEFT(C2,1)

In this case the value thats calculated in E2 is "3" but the VLookup only works if I literally type "3" into the Cell, I get an #N/A error otherwise

Upvotes: 2

Views: 9717

Answers (1)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

You shoudl use the value function like this:
=VALUE(LEFT(C2,1))

--OR--

=VLOOKUP(VALUE(E2),'Colour Code'!A$2:B$11, 2, FALSE)

The problem you are having is trying to match a number against a string. This formula converts text to numbers.

Upvotes: 4

Related Questions