# Look up formula from table with uneven format

1. ## Look up formula from table with uneven format

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

3. ## Re: Look up formula from table with uneven format

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))

4. ## Re: Look up formula from table with uneven format

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.

5. ## Re: Look up formula from table with uneven format

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.)

6. ## Re: Look up formula from table with uneven format

Thanks heaps Ali GW. That was really helpful

Apologies Alansidman.

7. ## Re: Look up formula from table with uneven format

Please also review post #2 and do as requested there.

You cannot ignore moderation requests.

8. ## Re: Look up formula from table with uneven format

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))

10. ## Re: Look up formula from table with uneven format

Can I ask how would the above formula shape up if I needed to reference a second cell
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!

11. ## Re: Look up formula from table with uneven format

Another solution that works too:

``Please Login or Register  to view this content.``

12. ## Re: Look up formula from table with uneven format

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

13. ## Re: Look up formula from table with uneven format

Upload new worksheet with new variable in B2 and B9:B12

15. ## Re: Look up formula from table with uneven format

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.

16. ## Re: Look up formula from table with uneven format

Thanks JetemC. This worked. I will go through formula and try to get my head around it

17. ## Re: Look up formula from table with uneven format

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.

18. ## Re: Look up formula from table with uneven format

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

19. ## Re: Look up formula from table with uneven format

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

20. ## Re: Look up formula from table with uneven format

thank...didn't seem to work as it references only one variable when I need to look up two

21. ## Re: Look up formula from table with uneven format

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

22. ## Re: Look up formula from table with uneven format

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.

23. ## Re: Look up formula from table with uneven format

thank you Jetemc.

24. ## Re: Look up formula from table with uneven format

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

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1