Vlookup for multiple values

[SCHM]

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!D3663,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

 

[lk]

Try:
=SUMPRODUCT(--(Formulae!$B$36:$B$63=A24),--(Formulae!$C$36:$C$63=B24),Formulae!$D$36:$D$63)

 

[SCHM]

Even after updating to this formula I get the same result $0. What does "--"
do?

Thanks for responding
SCHM

 

[lk]

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]

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.

 

[Ken Hudson]

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.

 

[SCHM]

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