INDEX MATCH and SQL refreshed tables

[dave_uk]

Hi,

I am revamping some spread sheets and have hit a brick wall.

At current I am using 2 spread sheets, 1 has a database of locations and items that's manually updated every day and the other sheet is used to import scanner data and lookup information on the first then print out details.

This worked fine until I incorporated SQL into the database spread sheet to automatically refresh the data when opened in a morning. INDEX and MATCH are being used to get details based on a unique item number but are now returning nothing. I repeated the index, match function but still no luck.

Now that the information is getting refreshed straight of a database instead of manually do I need to approach this differently?

 

[FormR]

Hi, can you post the formula you are using and examples of the unique item numbers?

It's possible that the the data being returned from your database is a different data type to your manually entered values (i.e. one is text and the other a number)

 

[RoryA]

Are you getting a #N/A error? It may be that the code is coming through as text rather than a number (or vice versa) so doesn't match what you are looking up.

 

[dave_uk]

This formula is being used for Bin Locations.

=INDEX('[Item Database.xlsx]livestock'!$F:$F,MATCH($B2,'[Item Database.xlsx]livestock'!$J:$J,0))

and is returning #N/A

 

[FormR]

Can you post a few examples of the values in B2?

Did you check if one is a number and one text?

 

[dave_uk]

Hi,

Sure here is the database list snapshot and formula based on below,


Sheet 1 - refreshed data sheet
Cross reference ($J:$J) formatted as a number, location ($F:$F)which is being returned formatted as text


Qty | Bin Code| Location | Item No_ | Special | Cross-Ref
2 W-70-K01 OLDHAM ITM136303 FLOOR 10 5052710363031
1 W-70-K01 OLDHAM ITM136397 FLOOR 10 5052710363970
13 W-70-K01 OLDHAM ITM136398 FLOOR 10 5052710363987
34 W-70-K01 OLDHAM ITM136399 FLOOR 10 5052710363994
61 W-70-K01 OLDHAM ITM136400 FLOOR 10 5052710364007
10 W-70-K01 OLDHAM ITM136401 FLOOR 10 5052710364014


sheet 2 - imported list sheet ($B2)

Formatted as a number also

Cross-Ref
5052710363031
5052710363970
5052710363987
5052710363994
5052710364007
5052710364014
5052710364021
5052710199951

Thanks.

 

[FormR]

If you put this in a spare cell on livestock sheet =ISNUMBER(J2) what does it return?

Does this work any better?

=INDEX('[Item Database.xlsx]livestock'!$F:$F,MATCH($B2&"",'[Item Database.xlsx]livestock'!$J:$J,0))

 

[RoryA]

If you don't manually set any alignment on the cross ref cells, are the numbers on each sheet displayed on the left or right of the cells?

 

[dave_uk]

If you put this in a spare cell on livestock sheet =ISNUMBER(J2) what does it return?

Does this work any better?

=INDEX('[Item Database.xlsx]livestock'!$F:$F,MATCH($B2&"",'[Item Database.xlsx]livestock'!$J:$J,0))

H Inserting the function into a spare cell returns FALSE

AND

adding the extra works

Although it now works could I ask what was the initial problem?

 

[FormR]

Although it now works could I ask what was the initial problem?

Hi, it's as we originally suspected, and as the ISNUMBER() test confirms:

It's possible that the the data being returned from your database is a different data type to your manually entered values (i.e. one is text and the other a number)

It may be that the code is coming through as text rather than a number (or vice versa) so doesn't match what you are looking up.

The &"" coerces your manually entered numeric value to a text value - it's important to note that simply changing the format of a cell will not automatically convert the underlying value.