• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use vlookup in a lookup table where there 2 identical lookup value

M

mrexcelrc

New Member
Hello, I need help with this formula. From the lookup table below in Sheet2, cells A1:D7, if I have a formula in cell A1 of Sheet1 as: =vlookup("50016A",Sheet2!$A$1:$D$7,4,FALSE) the formula will return a value of "FIXED" because vlookup will always look at the first lookup value and ignore the rest.


How would I edit this formula such that I only want it to return a value that is not "FIXED"? In other words for lookup value "50016A", I want the formula to return a value of "KENNY", lookup value "50017A" to return a value of "LIBOR" and 50018A a value of "LIBOR".


It seems to me that I would need an IF condition embedded in the vlookup formula. Please help! Thank you.


TradeId Type PorS INTEREST_dmIndex

50016A SWAP S FIXED

50016A SWAP P KENNY

50017A SWAP S LIBOR

50017A SWAP P FIXED

50018A SWAP S LIBOR

50018A SWAP P FIXED
 
Mrexcelrc


Firstly, Welcome to the Chandoo.org Forums


Try the following formula

Code: 
=INDEX(A2:A7,SUMPRODUCT((ROW(A2:A7))*(A2:A7=B11)*(D2:D7<>"Fixed"))-1)
 
Hi Hui,


Thanks for this suggestion. I am getting an error presumably because I need a value for cell B11. What would be the value for B11?
 
Hi,


I got it to work by making the formula:


INDEX(d2:d7,SUMPRODUCT((ROW(A2:A7))*(A2:A7=B11)*(D2:D7<>"Fixed"))-1


where B11 = TradeID


Thanks again.
 
Hello Hui,

I discovered that if there are more than 2 identical lookup values, the formula returns a #REF! value. Any suggestion on how to fix this?
 
Hi ,


Try the following :


=INDEX(D2:D7,(MATCH(1,(1*(A2:A7=B11)*(D2:D7<>"Fixed")),0)))


entered as an array formula i.e. with CTRL SHIFT ENTER.


Narayan
 
You must log in or register to reply here.
Back
Top