VLOOKUP or INDEX MATCH

[dboyd79]

I'm trying to pull data from another sheet for computer inventory details.

My trouble is having a lookup by s/n or product code, 2 different columns.

Sometimes we can't scan the s/n and wanted option to scan product code.

I was using

=INDEX(Master!A:J,MATCH(A:A,Master!A:A,0),3)

but need to look at column B for a match too.

 

[svendiamond]

=IF(ISNA(INDEX(Master!A:J,MATCH(A:A,MasterA:A,0),3)),INDEX(Master!A:J,MATCH(B:B,MasterB:B,0),3)

Does that work? What exactly are you trying to match? A:A in A:A ?

 

[dboyd79]

Getting an error with this formula

I'm trying to match A:A with A:A or B:B in another sheet called Master. Then return the data from column 3.

This is so I can scan bar codes into A:A and the data from the master gets filled in.

 

[dboyd79]

I think I got it working....thanks so much for putting me in right direction!

 

[dboyd79]

Well, it works when A:A matches Master sheet B:B

If A:A is in Master A:A, it returns FALSE

 

[svendiamond]

I don't understand how you're comparing two entire columns, rather than just one cell against an entire column. Can you show me what the data looks like? I highly recommend downloading the add-in Mr Excel HTML Maker from this link: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

It will allow you to easily copy and paste small clips of your sheet into the forum. It will make it much easier for someone to help you. The final result will look something like this:

Book1
	A	B
1	MyData	Value
2	Easy	1
3	To	2
4	Read	3
5	Data	4
Sheet3

 

[dboyd79]

Sorry, not able to download any add-ins here.

I'll try to explain further

This is for scanning devices for inventory. My sheet is blank and has a scan column for A. As I scan it drops down to the next line to scan the next one.

I have a Master sheet that holds all the details for these devices.

When I scan into column A, I want a formula to fill in the rest of the details from the master sheet.

I was using A:A so it can be the same formula throughout.

Hope that makes sense.

Thanks for your help!!!

 

[svendiamond]

When I scan into column A, I want a formula to fill in the rest of the details from the master sheet.

Please try to be more specific. I'm assuming you want columns B, C, D, etc. to automatically fill in the matching information from the master sheet. But what does the master sheet look like? What columns go where? The basic format for a simple INDEX/MATCH function is:

=INDEX(returnRange,MATCH(lookupValue,lookupRange,0))

So let's say you're scanning the barcode/SKU into cell A2, and column B is "Product Description" which you want to fill in automatically. And on the master sheet, "SKU" is column "J" and "Product Description" is column "P" -- you could use:

=INDEX(Master!P:P,MATCH(A2,Master!J:J,0))