Formatting issue disrupting vlookup/ index match

[coconutshake]

Screenshot.png

Hi Guys

First post ever on this forum so give me faith! I will return the good karma when my knowledge builds up!

So on the above attachment I am having an issue with trying to look up data on another tab.. I am 99% confident the syntax on the formula is correct and it is looking up data on another tab "AR"

Initially I used index match but have reverted to vlookup..
=IFERROR(INDEX(AR!E:E,MATCH(H6,AR!B:B,0)),0)
=IFERROR(VLOOKUP(H6,AR!$B:$E,4,FALSE),0)

There is the two formulae I used for the different types

So when this happens:
Screenshot2.png
Then the formula works but otherwise it doesn't!

Rather than looking up the value on column H I'd like it to look up column A instead but it just won't work and I don't know how to put the values in the format that works

Please help!!!! I will give you internet cookies and my eternal gratefulness!!

[ragulduy]

Upload an example of your worksheet, not a screenshot. Especially when the issue seems to be a formatting problem... You might have some success co-ercing the numbers stored as text, something like:

=IFERROR(INDEX(--AR!E:E,MATCH(--H6,--AR!B:B,0)),0)

as an array formula (confirm with ctrl+shift+enter)

[coconutshake]

Hey Ragulduy! Thank you for replying

I have attached the relevant tabs from the spreadsheet and put what I'd like it to do

Thank you so much for your help!

[coconutshake]

at the moment only J7:J8 return values but I had to manually go into the cell and click enter, ideally I would like it to just read the value from the pivot

[ragulduy]

Hi,

The formula in post #2 seems to work for your attachment.

Try putting:
=IFERROR(INDEX(AR!E:E,MATCH(A5,--AR!B:B,0)),0)

in J5 as an array formula (confirm with ctrl+shift+enter) and copy down.

The alternative would be to fix your data in the AR sheet so it is stored as numbers instead of text.

[Punnam]

Either u have to enter the data in Column H in text format or
If u want to enter in Number format use the background verification tab i.e I con and use convert to Numbers
Punnam

[coconutshake]

RAGULDUY YOU GENIUS!!!

I need to learn more about array formulas now! thank you so much this fixed the problem!!!

punnam also thank you for your help too

Is there anyway I can give you credit for this post?

[Punnam]

Ys can add some reputation points to one who helped you , by clicking the Tab "ADD Reputation" left bottom

Punnam