INDEX and MATCH returning #N/A

[Malvarlo]

Hi,
I'm attempting (and failing) to create a sheet that pulls data from another sheet to automatically populate once a cell has information entered into it.

I've tried VLOOKUP and HLOOKUP but kept getting a #N/A error, after a fair amount of Googling I read that using a INDEX and MATCH formula would have a better chance of working, but I'm getting the same error.
Here's the formula I'm using -

=INDEX(Sheet2!B3:B1048576,MATCH(C17,Sheet2!B3:D1048576,0))

I've spent far too long trying to work out where I've gone wrong but I've made no headway.

I'd greatly appreciate it if someone here could point out just what I've done wrong.

Thanks in advance!!

 

[Joe4]

The #N/A errors usually mean that no match is found.
In order for these formulas to work, there are two main things that have to happen (and most often lead to these #N/A errors):
1. The values must match EXACTLY. Something as simple as an extra space at the end of one of the value could cause them not to match (i.e. "DOG" and "DOG " will NOT match).
2. The values must be the exact same data type. Comparing numbers to letters will never result in a match. This is most commonly seen when one list is actually "numbers entered as text", which will never match up with "numbers entered as numbers".

If that doesn't help you, please post an example of one that you think SHOULD match.
Show us screen prints of the matching value (or what should be matching value) on each sheet.

 

[Malvarlo]

Hiya thanks for the speedy reply, the data it's referencing is serial numbers, for example - HM 1731 or 1958159. I've set all the relevant columns as text, because some of the serial numbers can start with a 0, which would be removed if I left the column as general. In all of the tests I did trying to get a valid response from the formula I copied a serial number straight from the data sheet, so there was no possibility of it not matching.

 

[Joe4]

Please show us a small sample of your data on each sheet!
Without seeing your data structure, it is very difficult for us to determine if you have written your formula correctly!

 

[Malvarlo]

I hope that helps, as you can see, I intend for the serial to be entered and then the manufacturer and model to be grabbed from the data sheet.

 

[Joe4]

Your formula does not look correct.

If you want to match on column D, but return column B, the formula should be:

=INDEX(Sheet2!B3:B1048576,MATCH(C17,Sheet2!D3:D1048576,0))

because you are looking for the match of cell C17 in column D on Sheet2, not column B.

 

[Malvarlo]

/facepalm. Thank you so much for the help, that works perfectly.

 

[Joe4]

You are welcome!
Glad I was able to help.