Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
Vlookup gives wrong answers when used in large data. Pls advise? | Excel Worksheet Functions | |||
If Statement linked to cell with VLOOKUP problem - getting wrong v | Excel Worksheet Functions | |||
Vlookup data wrong if the small value found are same | Excel Discussion (Misc queries) |