Looking for VLOOKUP Trick

[shdawson]

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?


Thanks,
S

 

[markmzz]

Look at my formula and at your formula (D2<>C2, G2<>F2):

my formula
=IF(SUM((D2=Detail!$F$2:$F$3)*(G2=Detail!$D$2:$D$3))=0,"",INDEX(Detail!$I$2:$I$3,SUM((D2=Detail!$F$2:$F$3)*(G2=Detail!$D$2:$D$3)*(ROW(Detail!$I$2:$I$3)-ROW(Detail!$I$1)))))

your formula
=IF(SUM((C2=Detail!$F$2:$F$1000)*(F2=Detail!$D$2:$D$1000))=0,"",INDEX(Detail!$I$2:$I$1000,SUM((C2=Detail!$F$2:$F$1000)*(F2=Detail!$D$2:$D$3)*(ROW(Detail!$I$2:$I$1000)-ROW(Detail!$I$1)))))

And this $D$3?

Your formula is correct?

When you can, do some test with the workbook that a send to you and tell me the results.

Markmzz

 

[Matty]

Could you try something for me? Against Worksheet Step 1 - All Roadmap Entries, in an unused Column, what does this formula return?

=ISNUMBER(F2)

Copied down to F20. And what does this formula return on Worksheet Detail, again entered in an empty Column:

=ISNUMBER(D2)

I think the problem is inconsitent data types, but the above will prove (or not) my theory.

Let me know...

Matty

 

[shdawson]

Mark,


Your formula works.

Note, that no duplicate entries can be in the lookup table. I cleansed those. Those that must remain return either a 0 or an incorrect answer....as expected.

I have annotated this in the report documentation and will have to live with it. Or, get the higher ups to put more information in their planning files so I can get a true unique identifier and not retro-fit something.

Finally, perhaps we can do more with MS Query. Dunno. I will live with this and be thankful.

Thank you very much.


Kindly,
SHD

 

[markmzz]

Shdawson,

I'm glad that the problem was resolved.

And thank you for the feedback.

Markmzz