• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP #N/A Error

dparteka

dparteka

Member
I'm baffled... in the attached spreadsheet cell C30 is a VLOOKUP formula that I can't figure out why it's not working, can someone please enlighten me, what am I doing wrong?
 

Attachments

  • VLOOKUP Error.xlsx
    37.4 KB · Views: 7
Well, that's because you are looking for Value "1" in column A, where only text is present.

What result are you expecting?
 
I thought what I was doing is looking in B2:E26 for the number "1" and in this case C30 would display the text in the first column which would be "Ken".
 
For that, you'd need something bit different. Since by default, VLOOKUP can only look in single column.

You can do something like...
=INDEX($A$2:$A$26,MATCH(1,SIGN((1=$B$2:$B$26)+(1=$C$2:$C$26)+(1=$D$2:$D$26)+(1=$E$2:$E$26)),0))

Confirmed as array (CSE).
 
SUMPRODUCT did the trick, thank you. Just for my own knowledge, if I was to use VLOOKUP and only look in column B to get the results from column A, why doesn't this work? =VLOOKUP(2,B2:B26,1,FALSE)... I'm looking for a number "2" in column B which should then result in displaying whatever is in the first column of the row that has the "2" in it... what is wrong with this formula?
 
It doesn't work because of the syntax of VLOOKUP
=VLOOKUP(this value, in this list, and get me value in this column, [is-my-list-sorted?])

Since the 1st column of B2:B26 is B, it returns the value from column B so you just get the value itself.
So when you use the formula =VLOOKUP(2,B2:B26,1,FALSE) it just returns 2

If you switch B and A, you can get the value from A by getting the second column:
=VLOOKUP(2,CHOOSE({2;1},A2:A26,B2:B26),2,FALSE)
 
You must log in or register to reply here.
Back
Top