To best of my knowledge, Lookup formula in Excel that effectively allow translating one range value to another does not have any parameter to dictate kind of match. VLookup has it, but Lookup doesn't.
Because of this, I am writing formula as follows
I only do Lookup if VLOOKUP has worked
Please Login or Register to view this content.
Raising query here to see if a better solution exists.
why not just use vlookup for the whole thing? or use index match
=index(target range,match(J7, SourceRange,0))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Instead of VLOOKUP you could probably use MATCH but I don't really understand what you are trying to do here.
Why use VLOOKUP then LOOKUP?
If posting code please use code tags, see here.
Yes!!! Thanks Norie!!! that was the solution I was trying to remember. I had forgotten MATCH and INDEX function.
martin <- I cannot use VLOOKUP in this case because Source and TargetRanges are not contiguous as required by VLOOKUPPlease Login or Register to view this content.
It was Martin that posted the INDEX/MATCH suggestion, not me.
OH!!
Thanks to Martin and Norie for help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks