CODEWITHSUNDEEP
excelvbavlookup
Si8
Si8

Reputation: 9225

How to have a VLookup in the same worksheet in Excel

I have the following Excel data:

    A          B           C
1 asdasd    asdasd     VLOOKUP("fg",$A$1:$A$11,2,FALSE)
2 asdasd    dfgd
3 asdasd    fghfgh
4 asdasd    tryrty
5 asdasd    456456
6 asdasd    45456
7 asdasd    456456
8 fgddgh    46fgtfgh
9 fghfgh    46456
10 dfgdfg   456546
11 fghfgh   456456

In the C column, I am getting a #N/A

What I am looking to achieve is if fg is in column A, show me the value of column B in column C.

How can I achieve that?

Upvotes: 0

Views: 1404

Answers (3)

ScotBirmingham
ScotBirmingham

Reputation: 50

You need to bracket your vlookup with if(iserror(vlookup() ), "", vlookup())

so

=if(iserror(VLOOKUP("fg",$A$1:$B$11,2,FALSE)),"",VLOOKUP("fg",$A$1:$B$11,2,FALSE))

Basically if it can't find fg, it throws an error. The if( iserror()) says if you get an error, return blank cell. If there's no error, (fg exists), return column 2.

In your current table, this function will return "" for all lines since none of them = "fg". If you want anything that contains, you can use wild cards someone else mentioned but it sounds like you want exact hits.

Upvotes: 1

Alex P
Alex P

Reputation: 12487

You get #N/A because "fg" in the VLOOKUP is searching for an exact match (i.e. "fg") and there isn't one in your column A.

Instead use a wildcard. Examples:

=VLOOKUP("fg",A2:B11,2,FALSE) //gets exact match

=VLOOKUP("*fg",A2:B11,2,FALSE) //matches when `fg` at the end e.g. `aaaafg`

=VLOOKUP("fg*",A2:B11,2,FALSE) //matches when `fg` at the start e.g. `fgaaaa`

=VLOOKUP("*fg*",A2:B11,2,FALSE) //matches when `fg` found in the entry e.g. `aafgaa`

Upvotes: 0

Michael Moulsdale
Michael Moulsdale

Reputation: 1488

you just need to change to

VLOOKUP("fg",$A$1:$B$11,2,FALSE)

Upvotes: 0

Related Questions