Vlookup help cross referencing cell against another sheet to return value of another cell

Alex p h

New Member
Joined
May 14, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Can someone please help I’m trying to make a order sheet that translates alternative part numbers to my part numbers. I have a full list of the numbers in one sheet and in main sheet I’m trying to use Vlookup to find the exact match of what I type in to a cell from the other sheet and if is found then returns what is in column a in that row it has been found in, but it just won’t work for some reason. Any help would be greatly appreciated
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,624
Office Version
  1. 365
Platform
  1. MacOS
do you have an example you can post here

what column are the part numbers in
Vlookup tends to work left to right
you say column A

Index(Sheet2!A:A, match( cell with part number to lookup , column with the part number in , 0 ))

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
 

Alex p h

New Member
Joined
May 14, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
do you have an example you can post here

what column are the part numbers in
Vlookup tends to work left to right
you say column A

Index(Sheet2!A:A, match( cell with part number to lookup , column with the part number in , 0 ))

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

Thank you any help is much appreciated. Basically I’m trying to make a customer order form that customer can fill in with whatever part numbers they are familiar with and it will translate to my part numbers
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,624
Office Version
  1. 365
Platform
  1. MacOS
try
=INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0))

is that what you needed

test (1).xlsx
ABC
1
2
3
4Part number for input to be cross referenced on other sheetPart number to retrieve from colum a on other sheet if match is found
5
6QtyPart NumberTruflow Part Number
7r5000-02-02B1F-02-02
8R5404-10-08B1M-08-10
order
Cell Formulas
RangeFormula
C7:C8C7=INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0))
 

Alex p h

New Member
Joined
May 14, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

try
=INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0))

is that what you needed

test (1).xlsx
ABC
1
2
3
4Part number for input to be cross referenced on other sheetPart number to retrieve from colum a on other sheet if match is found
5
6QtyPart NumberTruflow Part Number
7r5000-02-02B1F-02-02
8R5404-10-08B1M-08-10
order
Cell Formulas
RangeFormula
C7:C8C7=INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0))
Yes that’s it but I need to span fitting list b-I
Thank you soo much
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,624
Office Version
  1. 365
Platform
  1. MacOS
Yes that’s it but I need to span fitting list b-I
what does that mean - you want the full row returned

test (1).xlsx
ABCDEFGHIJ
1
2
3
4Part number for input to be cross referenced on other sheetPart number to retrieve from colum a on other sheet if match is found
5SizeRyco ParkerAquip Pirtek Child Hydraulink
6QtyPart NumberTruflow Part Number
7r5000-02-02B1F-02-021/8S26-02021/8GG33MSB2096-2W-02-02CB9-0202E-E-0202
8R5404-10-08B1M-08-101/2 x 5/8S27-10085/8-1/2FF33MS4008-8-10D-10-08CB1-1008A-A-0810
9
10
11
order
Cell Formulas
RangeFormula
C7:C8C7=INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0))
D7:J8D7=INDEX('fitting list'!C:C,MATCH(order!$B7,'fitting list'!$B:$B,0))
 

Alex p h

New Member
Joined
May 14, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

what does that mean - you want the full row returned

test (1).xlsx
ABCDEFGHIJ
1
2
3
4Part number for input to be cross referenced on other sheetPart number to retrieve from colum a on other sheet if match is found
5SizeRyco ParkerAquip Pirtek Child Hydraulink
6QtyPart NumberTruflow Part Number
7r5000-02-02B1F-02-021/8S26-02021/8GG33MSB2096-2W-02-02CB9-0202E-E-0202
8R5404-10-08B1M-08-101/2 x 5/8S27-10085/8-1/2FF33MS4008-8-10D-10-08CB1-1008A-A-0810
9
10
11
order
Cell Formulas
RangeFormula
C7:C8C7=INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0))
D7:J8D7=INDEX('fitting list'!C:C,MATCH(order!$B7,'fitting list'!$B:$B,0))
Sorry, to clarify in example if cell order b7 matches anything in fitting list columns b-I it returns the value of fittings list column a in that row
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,624
Office Version
  1. 365
Platform
  1. MacOS
Size column appears the same throughot - column C

so you want to return column A

if what every is entered into B7 exists in any column on the list sheet from column B to Column I

so size 1/8th - what happens
also you have other tables where D to I is duplicated but has different part numbers in A

I have added a count, and quite a few columns have 2 items or more

i think maybe using an IFERROR on each column , so it finds the first match
quite a nested lookup though

I'm sure there are betterways, i will have a think , sure i have something for finding a match in a grid


=IFERROR( INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0)), IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!C:C,0)), IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!D:D,0)), IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!E:E,0)),etc

test (1).xlsx
ABC
1
2
3
4Part number for input to be cross referenced on other sheetPart number to retrieve from colum a on other sheet if match is found
5
6QtyPart NumberTruflow Part Number
7S27-0202B1M-02-02
81/4-1/8FF33MSB1M-02-04
9A-A-0206B1M-02-06
10no Match
11no Match
12no Match
13no Match
14no Match
15no Match
16no Match
order
Cell Formulas
RangeFormula
C7:C16C7=IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!C:C,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!D:D,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!E:E,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!F:F,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!G:G,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!H:H,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!I:I,0)),"no Match"))))))))
 

Alex p h

New Member
Joined
May 14, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Size column appears the same throughot - column C

so you want to return column A

if what every is entered into B7 exists in any column on the list sheet from column B to Column I

so size 1/8th - what happens
also you have other tables where D to I is duplicated but has different part numbers in A

I have added a count, and quite a few columns have 2 items or more

i think maybe using an IFERROR on each column , so it finds the first match
quite a nested lookup though

I'm sure there are betterways, i will have a think , sure i have something for finding a match in a grid


=IFERROR( INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0)), IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!C:C,0)), IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!D:D,0)), IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!E:E,0)),etc

test (1).xlsx
ABC
1
2
3
4Part number for input to be cross referenced on other sheetPart number to retrieve from colum a on other sheet if match is found
5
6QtyPart NumberTruflow Part Number
7S27-0202B1M-02-02
81/4-1/8FF33MSB1M-02-04
9A-A-0206B1M-02-06
10no Match
11no Match
12no Match
13no Match
14no Match
15no Match
16no Match
order
Cell Formulas
RangeFormula
C7:C16C7=IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!C:C,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!D:D,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!E:E,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!F:F,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!G:G,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!H:H,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!I:I,0)),"no Match"))))))))
The size column I am still to remove but in column d the only duplicates should be n/a just for my reference that there is nothing available by that manufacture for that part
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,624
Office Version
  1. 365
Platform
  1. MacOS
ok, so remove the size column lookup part for column C

test (1).xlsx
ABC
1
2
3
4Part number for input to be cross referenced on other sheetPart number to retrieve from colum a on other sheet if match is found
5
6QtyPart NumberTruflow Part Number
7S27-0202B1M-02-02
81/4-1/8FF33MSB1M-02-04
9A-A-0206B1M-02-06
10no Match
11no Match
12no Match
13no Match
14no Match
15no Match
16no Match
17
order
Cell Formulas
RangeFormula
C7:C16C7=IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!B:B,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!D:D,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!E:E,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!F:F,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!G:G,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!H:H,0)),IFERROR(INDEX('fitting list'!A:A,MATCH(order!B7,'fitting list'!I:I,0)),"no Match")))))))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,605
Messages
5,832,677
Members
430,153
Latest member
Javid_P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top