how to search 2 columns for the information entered in one column

[nicody]

Good morning everyone,

I have a data spreadsheet that I am trying to retrieve information from.

The "data" sheet has:
Col A - item #/Product #
Col B - Product #/Item#
Col C - Item Description
Col D - Price

The "supply" sheet that the information is entered on has:
Col A - item #/Product # to look up
Col B - Item Description
Col C - Price
Col F - Item #
Col G - Product #

Currently I am using Vlookup and have put a double copy of the information with item# first and then a copy with product # first pasted below on the "data" sheet. This means if /when info changes I have to make sure that both sections get changed with the new information. Obviously with a data sheet of over 8000 lines, this is not an ideal situation.

What I need to have is a formula that will search Column A and B on the "data" sheet for the info entered in column A on the "supply" sheet and then autofill the other columns listed with the corresponding info. I feel like there should be a solution with the index/match functions but I can't seem to get it.

Appreciate any help you can offer,
Deb

[Limor_OP]

Hi,
Can you upload a sample file? Look at the yellow banner at the top of the page for more instructions.
Thanks.

[nicody]

I have uploaded a sample file.

Thanks,
Deb

[Limor_OP]

If you change the titles in your supply tab to match the text in the other tab -
You can apply the below formula in B2 :

=INDEX(data!$A$2:$D$19, MATCH(supply!$A$2,data!$A$2:$A$19,0), MATCH(supply!B1,data!$A$1:$D$1,0))

[nicody]

My apologies, I made a mistake on my sample file and directions as to what I was looking for.

I need the entry in column A on the "supply" sheet to search columns A and J on the "data" sheet for a match and then I need to populate the Column B, C, G and H on the "supply" sheet from that result. if I can get one of them populated from the search results, I can use the vlookup for the rest of the columns

I am attaching the revised file.

Thanks in advance for any help.
Deb

[Limor_OP]

To which column in the "Data" sheet does the "Scan" respond to?
I couldnt find any title with this text.

How would the scan column be populated?

[nicody]

The scan column is populated when a barcode for a product is scanned or a vendor product code is typed into the supply sheet. I then need that number to be searched for a match in column A or I. (Sorry not J as mentioned above, that was a typo).

Barcode references are in column A and Vendor product code is in column I. Product could be entered with either of the 2 codes depending on where they are picking the product from and if the barcode scanner is available.

Thanks
Deb

[Limor_OP]

Hi,
Your titles do not always match the data sheet, so I changed them accordingly. This is necessary becuase the index match searches by the title name and the item code.

In B2 and drag to the right:

=INDEX(data!$A$3:$S$7,MATCH($A$2,data!$A$3:$A$7,0), MATCH(supply!B$1,data!$A$2:$S$2,0))

I couldnt find the information related to your "AREA/UNIT" column so I left that empty .
If you want to fill it with same formula - then give it the name it carries in your data sheet.

[nicody]

I am not sure why I'm not getting this, I can usually figure this out pretty good but I can't seem to wrap my head around this one.

There is still a problem somewhere, the auto populated items in the spreadsheet are all the same in each column but should be dependent on the number in the scan column.

Also it also doesn't recognize a product out of column "I". ie. when I enter 574-EN95 (Catalog No from column I) into column A - everything changes to #N/A.


Columns A, D, E and F are not auto populated, they will be a scanned/manual entry item or a simple calculation.

Thanks for your help,
Deb

[Limor_OP]

Sorry, you are right, the formula was fixed on the 1st scan.
I amended it in post #8

Hope this will be it

[nicody]

That fixed it, thank you so much for your help.

Update:

It is working for the item number both scanned and manually entered wonderfully.

After inserting the formula into the worksheet, I realized that the formula still does not recognize when a product code from Column I is typed into column A. ie. when I enter 574-EN95 (Catalog No from column I) into column A - everything changes to #N/A.

I have inserted a picture below of how I need it to populate after the codes have been entered in column A on the supply sheet. Just as an FYI, my master data sheet is approximately 20 columns by 3800 rows and is likely to expand.

sample.png

Thanks again for any help you can offer,
Deb

[Limor_OP]

Hi Deb,

I missed the fact that you are often using the catalogue# in Scan column.
Please see below and attached the amended formula to include

In C2:

Please Login or Register  to view this content.

In D2:

Please Login or Register  to view this content.