Lookup 2 values to return 1 value

[TheBear]

Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).

I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.

I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..I am new to these functions and would appreciate your help.

Many Thanks
Glen

[Domenic]

Try...

G8, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=INDEX(A!$H$2:$H$83,MATCH(1,IF(A!$A$2:$A$83=$B8,IF(A!$B$2:$B$83=G$6,1)),0))

Hope this helps!

[mewingkitty]

As usual, I found a clumsy way to make it work, but it's not 2003 compatible. I'll post it on the off chance you have 2007.

Anyways, about Vlookup, the part that threw me off while trying to use it is the column reference.

ie.

Please Login or Register  to view this content.

will take the information in C10, and look for a match for it in the left-most column of A2:B83 (It always searches the first, aka leftmost column of the selected area for matching data). It will then return information found in the 2nd column of that highlighted area (so if it found a match for C10 in cell A25, it would show the information in B25). The FALSE means it's looking for an exact match, and not an approximate one.

Hope that helps!

[TheBear]

Domenic hi..I copied your function into G8 and confirmed (}) but the dreaded #N/A appeared - did this happen when you did it?

mewingkitty..thanks for your help, unfortunately I am using a 2003 version.

[martindwilson]

you have real numbers not text numbers in g6:l6
the rest of the two sheets are text numbers so you wont find a match

[mewingkitty]

Cause with

Please Login or Register  to view this content.

I get the attached picture as a result. Matches across the board.

[TheBear]

martindWilson - thanks for that observation I have now changed the formatting of the cells on both sheets to 'number', unfortunately I still can not get it to match!

mewingkitty - thanks for sending your screenshot, when I try your error check I can not get that to work either, I am not sure what I am doing wrong; do you think it is a compatibility error as I am still using 2003?

Thanks for your help
Glen

[NBVC]

If you fixed the formatting, you will still get #N/A in G8 because there are no matches for both conditions in sheet A.

Copy the formula across to L6 and you will find the last 2 should return numbers...

[TheBear]

NBVC-Hi, have copied across all cells and still see #N/A Any ideas?

Thanks
Glen

[NBVC]

See attached... I just converted column B in Sheet A by selecting the column and going to Data|Text to Columns and clicking Finish.

When you first enter the formula in G8, confirm it with CSE keys and then copy it across (and down).

Note: To get rid of those ugly #N/A formulas, use an error trap...

Replace formula in G8 with:

Please Login or Register  to view this content.

and then reconfirm it with CSE keys and then copy across and down.

[TheBear]

NBVC - Excellent, that works really well, thank you for that

Also, thanks to everyone who helped.

Glen