| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup for multiple values
Hello,
I am trying to lookup two values using vlookup and I want it to return one value. I have tried at least three of the solutions posted on this forum. None works. My data is on tab 'Formulae' as follows from Row 36 to 63. Sample as shown below. Col A has result of B&C concatenated. Col A Col B Col C Col D LCCY1ADLCURE LCCY1ADL CURE $- LCCY1ADLHR LCCY1ADL HR $3.95 LCCY1ADLLR LCCY1ADL LR $0.34 LCCY1ADLMHR LCCY1ADL MHR $2.07 LCCY1ADLMLR LCCY1ADL MLR $0.66 LCCY1ADLTOTAL LCCY1ADL TOTAL $0.88 LCCY1NEWCURE LCCY1NEW CURE $- LCCY1NEWHR LCCY1NEW HR $4.24 LCCY1NEWLR LCCY1NEW LR $2.78 LCCY1NEWMHR LCCY1NEW MHR $3.31 LCCY1NEWMLR LCCY1NEW MLR $2.87 On another tab, I have the inputs in A25 (say it has LCCY1NEW) and B25 (say it has MHR) to retrieve the value in cell G25 (which should be $3.31) 1) Concatenate: I am sometimes able to get results, but they are not the correct, else I get #N/A. (Says $ 0) Cell G25 has formula, =VLOOKUP((A24&B24),Formulae!$A$36:$F$63,4,FALSE) 2) Using Index and Match: result is #N/A {=INDEX(Formulae!D36:D63,MATCH(A24&B24, Formulae!B36:B63&Formulae!C36:C63,0))} 3) Using Sumproduct: Result is not correct (says $0) =SUMPRODUCT((Formulae!$B$36:$B$63=A24),(Formulae!$ C$36:$C$63=B24),Formulae!$D$36:$D$63) Appreciate anyone shedding light on the issue. Regards, SCHM |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup for multiple values
Try:
=SUMPRODUCT(--(Formulae!$B$36:$B$63=A24),--(Formulae!$C$36:$C$63=B24),Formulae!$D$36:$D$63) "SCHM" wrote: Hello, I am trying to lookup two values using vlookup and I want it to return one value. I have tried at least three of the solutions posted on this forum. None works. My data is on tab 'Formulae' as follows from Row 36 to 63. Sample as shown below. Col A has result of B&C concatenated. Col A Col B Col C Col D LCCY1ADLCURE LCCY1ADL CURE $- LCCY1ADLHR LCCY1ADL HR $3.95 LCCY1ADLLR LCCY1ADL LR $0.34 LCCY1ADLMHR LCCY1ADL MHR $2.07 LCCY1ADLMLR LCCY1ADL MLR $0.66 LCCY1ADLTOTAL LCCY1ADL TOTAL $0.88 LCCY1NEWCURE LCCY1NEW CURE $- LCCY1NEWHR LCCY1NEW HR $4.24 LCCY1NEWLR LCCY1NEW LR $2.78 LCCY1NEWMHR LCCY1NEW MHR $3.31 LCCY1NEWMLR LCCY1NEW MLR $2.87 On another tab, I have the inputs in A25 (say it has LCCY1NEW) and B25 (say it has MHR) to retrieve the value in cell G25 (which should be $3.31) 1) Concatenate: I am sometimes able to get results, but they are not the correct, else I get #N/A. (Says $ 0) Cell G25 has formula, =VLOOKUP((A24&B24),Formulae!$A$36:$F$63,4,FALSE) 2) Using Index and Match: result is #N/A {=INDEX(Formulae!D36:D63,MATCH(A24&B24, Formulae!B36:B63&Formulae!C36:C63,0))} 3) Using Sumproduct: Result is not correct (says $0) =SUMPRODUCT((Formulae!$B$36:$B$63=A24),(Formulae!$ C$36:$C$63=B24),Formulae!$D$36:$D$63) Appreciate anyone shedding light on the issue. Regards, SCHM |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup for multiple values
Even after updating to this formula I get the same result $0. What does "--"
do? Thanks for responding SCHM "lk" wrote: Try: =SUMPRODUCT(--(Formulae!$B$36:$B$63=A24),--(Formulae!$C$36:$C$63=B24),Formulae!$D$36:$D$63) "SCHM" wrote: Hello, I am trying to lookup two values using vlookup and I want it to return one value. I have tried at least three of the solutions posted on this forum. None works. My data is on tab 'Formulae' as follows from Row 36 to 63. Sample as shown below. Col A has result of B&C concatenated. Col A Col B Col C Col D LCCY1ADLCURE LCCY1ADL CURE $- LCCY1ADLHR LCCY1ADL HR $3.95 LCCY1ADLLR LCCY1ADL LR $0.34 LCCY1ADLMHR LCCY1ADL MHR $2.07 LCCY1ADLMLR LCCY1ADL MLR $0.66 LCCY1ADLTOTAL LCCY1ADL TOTAL $0.88 LCCY1NEWCURE LCCY1NEW CURE $- LCCY1NEWHR LCCY1NEW HR $4.24 LCCY1NEWLR LCCY1NEW LR $2.78 LCCY1NEWMHR LCCY1NEW MHR $3.31 LCCY1NEWMLR LCCY1NEW MLR $2.87 On another tab, I have the inputs in A25 (say it has LCCY1NEW) and B25 (say it has MHR) to retrieve the value in cell G25 (which should be $3.31) 1) Concatenate: I am sometimes able to get results, but they are not the correct, else I get #N/A. (Says $ 0) Cell G25 has formula, =VLOOKUP((A24&B24),Formulae!$A$36:$F$63,4,FALSE) 2) Using Index and Match: result is #N/A {=INDEX(Formulae!D36:D63,MATCH(A24&B24, Formulae!B36:B63&Formulae!C36:C63,0))} 3) Using Sumproduct: Result is not correct (says $0) =SUMPRODUCT((Formulae!$B$36:$B$63=A24),(Formulae!$ C$36:$C$63=B24),Formulae!$D$36:$D$63) Appreciate anyone shedding light on the issue. Regards, SCHM |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup for multiple values
It converts the argument to a 1 if true, 0 if false. I re-read you post, it
says your inputs are in A25, not A24. "SCHM" wrote: Even after updating to this formula I get the same result $0. What does "--" do? Thanks for responding SCHM "lk" wrote: Try: =SUMPRODUCT(--(Formulae!$B$36:$B$63=A24),--(Formulae!$C$36:$C$63=B24),Formulae!$D$36:$D$63) "SCHM" wrote: Hello, I am trying to lookup two values using vlookup and I want it to return one value. I have tried at least three of the solutions posted on this forum. None works. My data is on tab 'Formulae' as follows from Row 36 to 63. Sample as shown below. Col A has result of B&C concatenated. Col A Col B Col C Col D LCCY1ADLCURE LCCY1ADL CURE $- LCCY1ADLHR LCCY1ADL HR $3.95 LCCY1ADLLR LCCY1ADL LR $0.34 LCCY1ADLMHR LCCY1ADL MHR $2.07 LCCY1ADLMLR LCCY1ADL MLR $0.66 LCCY1ADLTOTAL LCCY1ADL TOTAL $0.88 LCCY1NEWCURE LCCY1NEW CURE $- LCCY1NEWHR LCCY1NEW HR $4.24 LCCY1NEWLR LCCY1NEW LR $2.78 LCCY1NEWMHR LCCY1NEW MHR $3.31 LCCY1NEWMLR LCCY1NEW MLR $2.87 On another tab, I have the inputs in A25 (say it has LCCY1NEW) and B25 (say it has MHR) to retrieve the value in cell G25 (which should be $3.31) 1) Concatenate: I am sometimes able to get results, but they are not the correct, else I get #N/A. (Says $ 0) Cell G25 has formula, =VLOOKUP((A24&B24),Formulae!$A$36:$F$63,4,FALSE) 2) Using Index and Match: result is #N/A {=INDEX(Formulae!D36:D63,MATCH(A24&B24, Formulae!B36:B63&Formulae!C36:C63,0))} 3) Using Sumproduct: Result is not correct (says $0) =SUMPRODUCT((Formulae!$B$36:$B$63=A24),(Formulae!$ C$36:$C$63=B24),Formulae!$D$36:$D$63) Appreciate anyone shedding light on the issue. Regards, SCHM |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup for multiple values
I have inputs in 4 of the rows (24-27) that I am trying different formulae in
Col G. All of them have the same inputs. "lk" wrote: It converts the argument to a 1 if true, 0 if false. I re-read you post, it says your inputs are in A25, not A24. "SCHM" wrote: Even after updating to this formula I get the same result $0. What does "--" do? Thanks for responding SCHM "lk" wrote: Try: =SUMPRODUCT(--(Formulae!$B$36:$B$63=A24),--(Formulae!$C$36:$C$63=B24),Formulae!$D$36:$D$63) "SCHM" wrote: Hello, I am trying to lookup two values using vlookup and I want it to return one value. I have tried at least three of the solutions posted on this forum. None works. My data is on tab 'Formulae' as follows from Row 36 to 63. Sample as shown below. Col A has result of B&C concatenated. Col A Col B Col C Col D LCCY1ADLCURE LCCY1ADL CURE $- LCCY1ADLHR LCCY1ADL HR $3.95 LCCY1ADLLR LCCY1ADL LR $0.34 LCCY1ADLMHR LCCY1ADL MHR $2.07 LCCY1ADLMLR LCCY1ADL MLR $0.66 LCCY1ADLTOTAL LCCY1ADL TOTAL $0.88 LCCY1NEWCURE LCCY1NEW CURE $- LCCY1NEWHR LCCY1NEW HR $4.24 LCCY1NEWLR LCCY1NEW LR $2.78 LCCY1NEWMHR LCCY1NEW MHR $3.31 LCCY1NEWMLR LCCY1NEW MLR $2.87 On another tab, I have the inputs in A25 (say it has LCCY1NEW) and B25 (say it has MHR) to retrieve the value in cell G25 (which should be $3.31) 1) Concatenate: I am sometimes able to get results, but they are not the correct, else I get #N/A. (Says $ 0) Cell G25 has formula, =VLOOKUP((A24&B24),Formulae!$A$36:$F$63,4,FALSE) 2) Using Index and Match: result is #N/A {=INDEX(Formulae!D36:D63,MATCH(A24&B24, Formulae!B36:B63&Formulae!C36:C63,0))} 3) Using Sumproduct: Result is not correct (says $0) =SUMPRODUCT((Formulae!$B$36:$B$63=A24),(Formulae!$ C$36:$C$63=B24),Formulae!$D$36:$D$63) Appreciate anyone shedding light on the issue. Regards, SCHM |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup for multiple values
Hi,
It looks to me like VLOOKUP should be working. The Formulae sheet has the data concatenated in column A. On Sheet2 you have LCCY1NEW in A25 and MHR in B25. In G25 of Sheet2 you have: =VLOOKUP(A25&B25,Formulae!$A$36:$F$63,4,False) G25 should return $3.31. If it doesn't, my guess is that you have an extra hidden space or two in one or more cells. You can test by using =LEN(A25) and =LEN(B25) for the cells on Sheet2. Compare that total to the length of the matching cell in the Formulae sheet. -- Ken Hudson "SCHM" wrote: Hello, I am trying to lookup two values using vlookup and I want it to return one value. I have tried at least three of the solutions posted on this forum. None works. My data is on tab 'Formulae' as follows from Row 36 to 63. Sample as shown below. Col A has result of B&C concatenated. Col A Col B Col C Col D LCCY1ADLCURE LCCY1ADL CURE $- LCCY1ADLHR LCCY1ADL HR $3.95 LCCY1ADLLR LCCY1ADL LR $0.34 LCCY1ADLMHR LCCY1ADL MHR $2.07 LCCY1ADLMLR LCCY1ADL MLR $0.66 LCCY1ADLTOTAL LCCY1ADL TOTAL $0.88 LCCY1NEWCURE LCCY1NEW CURE $- LCCY1NEWHR LCCY1NEW HR $4.24 LCCY1NEWLR LCCY1NEW LR $2.78 LCCY1NEWMHR LCCY1NEW MHR $3.31 LCCY1NEWMLR LCCY1NEW MLR $2.87 On another tab, I have the inputs in A25 (say it has LCCY1NEW) and B25 (say it has MHR) to retrieve the value in cell G25 (which should be $3.31) 1) Concatenate: I am sometimes able to get results, but they are not the correct, else I get #N/A. (Says $ 0) Cell G25 has formula, =VLOOKUP((A24&B24),Formulae!$A$36:$F$63,4,FALSE) 2) Using Index and Match: result is #N/A {=INDEX(Formulae!D36:D63,MATCH(A24&B24, Formulae!B36:B63&Formulae!C36:C63,0))} 3) Using Sumproduct: Result is not correct (says $0) =SUMPRODUCT((Formulae!$B$36:$B$63=A24),(Formulae!$ C$36:$C$63=B24),Formulae!$D$36:$D$63) Appreciate anyone shedding light on the issue. Regards, SCHM |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup for multiple values
I did find empty spaces in my Formulae worksheet. Once that was fixed, it
worked like a charm... I cannot believe that such a simple thing caused me to spend several hours wondering why my formula wouldnt work. Thanks Ken. - SCHM "Ken Hudson" wrote: Hi, It looks to me like VLOOKUP should be working. The Formulae sheet has the data concatenated in column A. On Sheet2 you have LCCY1NEW in A25 and MHR in B25. In G25 of Sheet2 you have: =VLOOKUP(A25&B25,Formulae!$A$36:$F$63,4,False) G25 should return $3.31. If it doesn't, my guess is that you have an extra hidden space or two in one or more cells. You can test by using =LEN(A25) and =LEN(B25) for the cells on Sheet2. Compare that total to the length of the matching cell in the Formulae sheet. -- Ken Hudson "SCHM" wrote: Hello, I am trying to lookup two values using vlookup and I want it to return one value. I have tried at least three of the solutions posted on this forum. None works. My data is on tab 'Formulae' as follows from Row 36 to 63. Sample as shown below. Col A has result of B&C concatenated. Col A Col B Col C Col D LCCY1ADLCURE LCCY1ADL CURE $- LCCY1ADLHR LCCY1ADL HR $3.95 LCCY1ADLLR LCCY1ADL LR $0.34 LCCY1ADLMHR LCCY1ADL MHR $2.07 LCCY1ADLMLR LCCY1ADL MLR $0.66 LCCY1ADLTOTAL LCCY1ADL TOTAL $0.88 LCCY1NEWCURE LCCY1NEW CURE $- LCCY1NEWHR LCCY1NEW HR $4.24 LCCY1NEWLR LCCY1NEW LR $2.78 LCCY1NEWMHR LCCY1NEW MHR $3.31 LCCY1NEWMLR LCCY1NEW MLR $2.87 On another tab, I have the inputs in A25 (say it has LCCY1NEW) and B25 (say it has MHR) to retrieve the value in cell G25 (which should be $3.31) 1) Concatenate: I am sometimes able to get results, but they are not the correct, else I get #N/A. (Says $ 0) Cell G25 has formula, =VLOOKUP((A24&B24),Formulae!$A$36:$F$63,4,FALSE) 2) Using Index and Match: result is #N/A {=INDEX(Formulae!D36:D63,MATCH(A24&B24, Formulae!B36:B63&Formulae!C36:C63,0))} 3) Using Sumproduct: Result is not correct (says $0) =SUMPRODUCT((Formulae!$B$36:$B$63=A24),(Formulae!$ C$36:$C$63=B24),Formulae!$D$36:$D$63) Appreciate anyone shedding light on the issue. Regards, SCHM |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookup Null values | Excel Worksheet Functions | |||
| use vlookup or other to find the nearest values (<) or interpola | Excel Worksheet Functions | |||
| Vlookup on a worksheet with similar values | Excel Worksheet Functions | |||
| vlookup returning multiple values | Excel Worksheet Functions | |||
| VLOOKUP FOR MULTIPLE VALUES | Setting up and Configuration of Excel | |||
Hybrid Mode
