Vlookup - can't figure out what's wrong
 

I've tried to illustrate my problem:

"NameID" is A1, "rs10170160" is B1, etc. Array data starts at A35

Name ID€¦..rs10170160
70001-1€¦..(formula here)

Formula: =vlookup(A2&B$1,A$35:E$38,4,False)

Array: (note, column C here is programmed in as =A35&B35)

70001-1€¦..rs10170160€¦..70001-1rs10170160€¦..C€¦..T
70001-11€¦..rs10170160€¦..70001-11rs10170160€¦..C€¦..T
70002-1€¦..rs10170160€¦..70002-1rs10170160€¦..C€¦..T
70002-13.....rs10170160.....70002-13rs10170160.....T.....T

Why does my formula keep returning NA when it should return a "C"??

Thanks in advance!

Vlookup - can't figure out what's wrong
 

It looks ok, I'd start by checking your data such as =(A2&$B$1)=E37 or
whichever column it should match, if it comes back false you have an extra
space or different data types or something, that would be the first thing to
check.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"pm" wrote:

I've tried to illustrate my problem:

"NameID" is A1, "rs10170160" is B1, etc. Array data starts at A35

Name ID€¦..rs10170160
70001-1€¦..(formula here)

Formula: =vlookup(A2&B$1,A$35:E$38,4,False)

Array: (note, column C here is programmed in as =A35&B35)

70001-1€¦..rs10170160€¦..70001-1rs10170160€¦..C€¦..T
70001-11€¦..rs10170160€¦..70001-11rs10170160€¦..C€¦..T
70002-1€¦..rs10170160€¦..70002-1rs10170160€¦..C€¦..T
70002-13.....rs10170160.....70002-13rs10170160.....T.....T

Why does my formula keep returning NA when it should return a "C"??

Thanks in advance!

Vlookup - can't figure out what's wrong
 

You have a few options....Here are two:

First, if you want to VLOOKUP a concatenated combination of the values from
A2 and B1, then you'll need the left column of the lookup range to contain
those values.

You could achieve that by doing this:
1)Insert a cells at the front of the range (shifting Col_A to the right)
2)A35: =B35&C35 (copy that formula down)
3)Your new formula in B2 would change to:
=VLOOKUP(A2&B$1,A$35:E$38,5,False)

OR....
You could just use this formula with your current structure
B2: =INDEX(D:D,SUMPRODUCT((A35:A38=A2)*(B35:B38=B1)*RO W(D35:D38)))

Notes:
If there will be duplicate matching combinations, the first formula will
return the first "hit". The second formula will be completely wrong. There
are workarounds to prevent that, so let us know if you run into issues with
duplicates.

Is that something you can work with?
Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"pm" wrote:

I've tried to illustrate my problem:

"NameID" is A1, "rs10170160" is B1, etc. Array data starts at A35

Name ID€¦..rs10170160
70001-1€¦..(formula here)

Formula: =vlookup(A2&B$1,A$35:E$38,4,False)

Array: (note, column C here is programmed in as =A35&B35)

70001-1€¦..rs10170160€¦..70001-1rs10170160€¦..C€¦..T
70001-11€¦..rs10170160€¦..70001-11rs10170160€¦..C€¦..T
70002-1€¦..rs10170160€¦..70002-1rs10170160€¦..C€¦..T
70002-13.....rs10170160.....70002-13rs10170160.....T.....T

Why does my formula keep returning NA when it should return a "C"??

Thanks in advance!

Vlookup - can't figure out what's wrong
 

The lookup value has to be in the far left column of your lookup range.
Either adjust your lookup range or move the concatenated field so its in the
left most column.

=VLOOKUP(A2&B$1,C$35:E$38,2.False)

Hope this helps.

Bill Horton

"pm" wrote:

I've tried to illustrate my problem:

"NameID" is A1, "rs10170160" is B1, etc. Array data starts at A35

Name ID€¦..rs10170160
70001-1€¦..(formula here)

Formula: =vlookup(A2&B$1,A$35:E$38,4,False)

Array: (note, column C here is programmed in as =A35&B35)

70001-1€¦..rs10170160€¦..70001-1rs10170160€¦..C€¦..T
70001-11€¦..rs10170160€¦..70001-11rs10170160€¦..C€¦..T
70002-1€¦..rs10170160€¦..70002-1rs10170160€¦..C€¦..T
70002-13.....rs10170160.....70002-13rs10170160.....T.....T

Why does my formula keep returning NA when it should return a "C"??

Thanks in advance!

Vlookup - can't figure out what's wrong
 

I just noticed that you've already got the concatenated values in Col_C of
your list.
So...you could just change your formula to
=VLOOKUP(A2&B$1,$C$35:$E$38,2,0)

(I believe Mr. Horton picked up on that, already)
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

You have a few options....Here are two:

First, if you want to VLOOKUP a concatenated combination of the values from
A2 and B1, then you'll need the left column of the lookup range to contain
those values.

You could achieve that by doing this:
1)Insert a cells at the front of the range (shifting Col_A to the right)
2)A35: =B35&C35 (copy that formula down)
3)Your new formula in B2 would change to:
=VLOOKUP(A2&B$1,A$35:E$38,5,False)

OR....
You could just use this formula with your current structure
B2: =INDEX(D:D,SUMPRODUCT((A35:A38=A2)*(B35:B38=B1)*RO W(D35:D38)))

Notes:
If there will be duplicate matching combinations, the first formula will
return the first "hit". The second formula will be completely wrong. There
are workarounds to prevent that, so let us know if you run into issues with
duplicates.

Is that something you can work with?
Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"pm" wrote:

I've tried to illustrate my problem:

"NameID" is A1, "rs10170160" is B1, etc. Array data starts at A35

Name ID€¦..rs10170160
70001-1€¦..(formula here)

Formula: =vlookup(A2&B$1,A$35:E$38,4,False)

Array: (note, column C here is programmed in as =A35&B35)

70001-1€¦..rs10170160€¦..70001-1rs10170160€¦..C€¦..T
70001-11€¦..rs10170160€¦..70001-11rs10170160€¦..C€¦..T
70002-1€¦..rs10170160€¦..70002-1rs10170160€¦..C€¦..T
70002-13.....rs10170160.....70002-13rs10170160.....T.....T

Why does my formula keep returning NA when it should return a "C"??

Thanks in advance!

Vlookup - can't figure out what's wrong
 

Thanks very much. Worked like a charm. Didn't realize it had to be in the
1st column!

"Ron Coderre" wrote:

I just noticed that you've already got the concatenated values in Col_C of
your list.
So...you could just change your formula to
=VLOOKUP(A2&B$1,$C$35:$E$38,2,0)

(I believe Mr. Horton picked up on that, already)
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

You have a few options....Here are two:

First, if you want to VLOOKUP a concatenated combination of the values from
A2 and B1, then you'll need the left column of the lookup range to contain
those values.

You could achieve that by doing this:
1)Insert a cells at the front of the range (shifting Col_A to the right)
2)A35: =B35&C35 (copy that formula down)
3)Your new formula in B2 would change to:
=VLOOKUP(A2&B$1,A$35:E$38,5,False)

OR....
You could just use this formula with your current structure
B2: =INDEX(D:D,SUMPRODUCT((A35:A38=A2)*(B35:B38=B1)*RO W(D35:D38)))

Notes:
If there will be duplicate matching combinations, the first formula will
return the first "hit". The second formula will be completely wrong. There
are workarounds to prevent that, so let us know if you run into issues with
duplicates.

Is that something you can work with?
Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"pm" wrote:

I've tried to illustrate my problem:

"NameID" is A1, "rs10170160" is B1, etc. Array data starts at A35

Name ID€¦..rs10170160
70001-1€¦..(formula here)

Formula: =vlookup(A2&B$1,A$35:E$38,4,False)

Array: (note, column C here is programmed in as =A35&B35)

70001-1€¦..rs10170160€¦..70001-1rs10170160€¦..C€¦..T
70001-11€¦..rs10170160€¦..70001-11rs10170160€¦..C€¦..T
70002-1€¦..rs10170160€¦..70002-1rs10170160€¦..C€¦..T
70002-13.....rs10170160.....70002-13rs10170160.....T.....T

Why does my formula keep returning NA when it should return a "C"??

Thanks in advance!