Simple V Look-Up / X Look-Up

[ap_terminator]

Hi All,

I'm try to do a really simple look up for and the look up array is a single column of numbers. See attached screenshots, Im trying to look up the values in column G in column W of another tab but it returns N/A for numbers that are clearly in Column W

Can anyone help? I feel like I'm missing a simple thing. Also i tried to used a match function but still have the same issue.

Thanks.

 

[ap_terminator]

Also if anyone has a better way of doing this, please share?
Always on the look out to do things quicker in excel and learn new ways.

 

[Peter_SSs]

My first guess is that the numbers (or at least some of them) in column G or column W of 'FY22' contain decimals, but the column is formatted to show whole numbers only.
So numbers that look the same in the two columns may differ by small decimal amounts and hence the XLOOKUP does not match them.
Could that be the case?

 

[ap_terminator]

My first guess is that the numbers (or at least some of them) in column G or column W of 'FY22' contain decimals, but the column is formatted to show whole numbers only.
So numbers that look the same in the two columns may differ by small decimal amounts and hence the XLOOKUP does not match them.
Could that be the case?

The numbers in both columns have decimals that are exactly the same. Will try and play around with the format and see.

 

[Peter_SSs]

What happens if you try this formula?

=XLOOKUP(ROUND(G9:G4015,0),ROUND('FY22'!W11:W1197,0),ROUND('FY22'!W11:W1197,0))

 

[ap_terminator]

What happens if you try this formula?

=XLOOKUP(ROUND(G9:G4015,0),ROUND('FY22'!W11:W1197,0),ROUND('FY22'!W11:W1197,0))

Hi,

Forgot to reply saying thanks, that works.

Does x look up or v look up not work with decimals? Still unsure why it wasn't working first time around.

 

[Peter_SSs]

Does x look up or v look up not work with decimals?

They both work with decimals.

Still unsure why it wasn't working first time around.

If it works with ROUND and does not work without it then the numbers that you think are equal, are not.