Used to do this all the time and now that I've been away, I've forgotten why this won't work!
Really need it to extract from 1 WORKBOOK to a different WORKBOOK but out of desperation, I'm testing 1 workbook trying to simply extract from SHEET 1 to SHEET 2 for simplicity using an INDEX (look up) and MATCH function.
If I recall correctly, I can NOT use LOOKUP unless the data resides in the 1st column... so, please help advise why I'm getting "N/A" with the following formula:
=INDEX(Sheet1!B2:B5,MATCH(C2,Sheet1!C2:C5,FALSE),1)
I used to be able to post image/file attachments in past years - not sure why I am unable today. Sorry, I know those are extremely helpful!
HERE's a TEXT VISUAL INSTEAD:
SHEET1
.....A...........B..........C
1..VENDOR...BOOTH...RATING
2..abc.........555.......gold
3..ggg.........333.......plat
4..fff...........111.......silver
5..zzz..........222.......bronze
SHEET 2 (this is the sheet that's trying to extract data into it by looking into Sheet 1 at the "BOOTH" number and if a MATCH is found, it extracts over the "RATING" (gold, silver etc) and pastes it into Sheet2 column
.....A............B.................C..........D.....
1..VENDOR...ADDRESS.....BOOTH....RATING
2..abc.........132kjkjs.......555........#N/A <<<< this D2 cell should say "gold"
3..ggg.........23439fkf......333 ........#N/A
4..fff...........3493xx........111 ........#N/A
5..zzz..........943 eros......222........#N/A
BY THE WAY, I've tested both sheets to verify that the numbers shown for the "BOOTH" numbers are TRUE numbers using the following:
=ISNUMBER(C2) and it returns "TRUE"
I also chunked the original file and created this test file from scratch to avoid any issues of trailing spaces, etc.... (I manually entered this data and did not copy/paste)
I even tried 'defining a name range' in the other files and had no luck (thinking that the formula may like using a named range better than long file and sheet name refs)... anyhow, I've exhausted my ideas on what it might be...
I've read that the above are common issues that would cause the "N/A" but eliminated those as possibilities before requesting your help.
Thanks so much!
Chris
Really need it to extract from 1 WORKBOOK to a different WORKBOOK but out of desperation, I'm testing 1 workbook trying to simply extract from SHEET 1 to SHEET 2 for simplicity using an INDEX (look up) and MATCH function.
If I recall correctly, I can NOT use LOOKUP unless the data resides in the 1st column... so, please help advise why I'm getting "N/A" with the following formula:
=INDEX(Sheet1!B2:B5,MATCH(C2,Sheet1!C2:C5,FALSE),1)
I used to be able to post image/file attachments in past years - not sure why I am unable today. Sorry, I know those are extremely helpful!
HERE's a TEXT VISUAL INSTEAD:
SHEET1
.....A...........B..........C
1..VENDOR...BOOTH...RATING
2..abc.........555.......gold
3..ggg.........333.......plat
4..fff...........111.......silver
5..zzz..........222.......bronze
SHEET 2 (this is the sheet that's trying to extract data into it by looking into Sheet 1 at the "BOOTH" number and if a MATCH is found, it extracts over the "RATING" (gold, silver etc) and pastes it into Sheet2 column
.....A............B.................C..........D.....
1..VENDOR...ADDRESS.....BOOTH....RATING
2..abc.........132kjkjs.......555........#N/A <<<< this D2 cell should say "gold"
3..ggg.........23439fkf......333 ........#N/A
4..fff...........3493xx........111 ........#N/A
5..zzz..........943 eros......222........#N/A
BY THE WAY, I've tested both sheets to verify that the numbers shown for the "BOOTH" numbers are TRUE numbers using the following:
=ISNUMBER(C2) and it returns "TRUE"
I also chunked the original file and created this test file from scratch to avoid any issues of trailing spaces, etc.... (I manually entered this data and did not copy/paste)
I even tried 'defining a name range' in the other files and had no luck (thinking that the formula may like using a named range better than long file and sheet name refs)... anyhow, I've exhausted my ideas on what it might be...
I've read that the above are common issues that would cause the "N/A" but eliminated those as possibilities before requesting your help.
Thanks so much!
Chris
Last edited: