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
Last edited by NBVC; 11-21-2008 at 07:47 AM.
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!
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.
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.
=VLOOKUP(C10,A2:B83,2,FALSE)
Hope that helps!
Last edited by mewingkitty; 11-19-2008 at 08:23 PM.
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.
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
Cause with
I get the attached picture as a result. Matches across the board.=IFERROR(IF(EXACT(VLOOKUP($B85,A!$A$2:$B$83,2,FALSE),J$6)=TRUE,"X",""),"")
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
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...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
NBVC-Hi, have copied across all cells and still see #N/A Any ideas?
Thanks
Glen
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:
and then reconfirm it with CSE keys and then copy across and down.=IF(ISNUMBER(MATCH(1,IF(A!$A$2:$A$83=$B8,IF(A!$B$2:$B$83=G$6,1)),0)),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)),"")
NBVC - Excellent, that works really well, thank you for that
Also, thanks to everyone who helped.
Glen
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks