Hi Friends,
I need help in building a lookup formula (index match)
issue here is the look up values are different in formats in source table and output table
see attached please. Without changing formats in either source table or output table, can we get a straight formula that can automatically pick up uneven formats
thanks
Please update your profile with your current version of Excel.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
This will cover all but the last row:
=IFERROR(IFERROR(IFNA(VLOOKUP(VALUE(A2),$A$9:$B$12,2,0),VLOOKUP(A2&"*",$A$9:$B$12,2,0)),VLOOKUP(VALUE(A2)&"*",$A$9:$B$12,2,0)),VLOOKUP(A2,$A$9:$B$12,2,0))
This covers everything:
=IFERROR(IFERROR(IFERROR(IFNA(VLOOKUP(VALUE(A2),$A$9:$B$12,2,0),VLOOKUP(A2&"*",$A$9:$B$12,2,0)),VLOOKUP(VALUE(A2)&"*",$A$9:$B$12,2,0)),VLOOKUP(A2,$A$9:$B$12,2,0)),VLOOKUP("*"&A2,$A$9:$B$12,2,0))
HOWEVER, it's a workaround that does not tackle the fundamental underlying issue of mismatched data - your best long-term course of action would be to tackle the data at source.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. No help to be offered until the link is provided.)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks heaps Ali GW. That was really helpful
Apologies Alansidman.
The apology is not enough - please provide the link requested.
Please also review post #2 and do as requested there.
You cannot ignore moderation requests.
Hi AliG, Can I ask how would the above formula shape up if I needed to reference a second cell
If formats were consistent I would have used below
I would normally use INDEX(Base!$AN$2:$AN$399,MATCH($I11&$P11,Base!$D$2:$D$399&Base!$C$2:$C$399,0))
OK ALi G. See cross link https://www.mrexcel.com/board/thread...ormat.1138771/
Last edited by AliGW; 07-02-2020 at 04:33 AM.
Thanks for the link.
I'm not even going to consider going there. Please re-read the third line of post #4: you are making your life far too difficult by not addressing the elephant in the room. I suggest you sort out the cause of your issues rather than trying to work round it and them. Good luck!Can I ask how would the above formula shape up if I needed to reference a second cell
Another solution that works too:
Please Login or Register to view this content.
Last edited by bebo021999; 07-02-2020 at 05:45 AM. Reason: add *
Quang PT
thank you bebo that was really helpful. could you also advise how the formaula would shape up if I need to reference to a second variable/criteria
in my initial example we are looking up A2 in range A9:A12
I need to reference to one more variable- say B2 in range B9:B12
thanks heaps
Upload new worksheet with new variable in B2 and B9:B12
HI Bebo, Please find attached
Please try the following modification of bebo's formula:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks JetemC. This worked. I will go through formula and try to get my head around it
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. Using the Evaluate Formula feature may help in understanding how the formula works. I hope that you have a blessed day.
HI Jtmc
I noticed that when I remove the alphabet next to the number in lookup table, answer references only to the number and ignore the alphabet and returns same answer
that is if we need to look up answer to 03004 and the look up table has 03004(B)...answer returned is for 03004....not a great concern for now...but wondering how the formula can be adjusted
Please see attached
Hi, sorry. My excel is portuguese and I have to sleep now, but this resolve your issue.
=SEERRO(PROCV(VALOR(A2);$A$8:$B$12;2;0);PROCV(A2;$A$8:$B$12;2;0))
See you, it is my firt day here
thank...didn't seem to work as it references only one variable when I need to look up two
Did you drag down my formula?
'Cause I can not understand what more you need, if you could explain more
Probably I can not understand 'cause my bad english but I am praticing every day and I wanna help you
Plese try to explain again to me
See you
The following formula seems to display the correct values:Formula:Please Login or Register to view this content.
For future reference please provide the correct values for instances where the formula produces an erroneous result so that we can verify the results of our corrected formulas/code.
Let us know if you have any questions.
thank you Jetemc.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks