Help with VLOOKUP possibly

clares

Well-known Member
Joined
Mar 14, 2002
Messages
557
Hi All

I am struggling with a IF and VLOOKUP (I'm not sure if this is the best option, perhaps someone may have a better suggestion).

What I am trying to do is return a value from a list to another list, where the names are matched.

I have one workbook with two worksheets. On the first worksheet I have a list of 12 names using 12 rows, say A1:A12 (this will never change), and on the second worksheet I have one column say Column A1:A6 with 6 names and in column C1:C6, I have the remaining 6 names. This is one team playing another team. In the next 12 columns, the first 6 columns (D1:J6) are scores ect for the team in column A, and the foloowing 6 columns (K1:O6) relate to the team named in column C.

What I want to do is use the formula which takes the name from the list on the first worksheet, matches it with the name in column A of the second worksheet, if it finds a match, then returns a value from column D:J, if it doesn't find a match in column A, looks in Column C, if it finds a match in column C returns a value from columns K:O. There will always be a match in either column A or C

Is this possible please?

Any help given is much appreciated

Kind Regards

Peter
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Let's say it finds the name in A1. What do you want to return from D:J? What is in those cells?

In other words, how do you identify what column to return from D1:J1?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
clares said:
Hi All

I am struggling with a IF and VLOOKUP (I'm not sure if this is the best option, perhaps someone may have a better suggestion).

What I am trying to do is return a value from a list to another list, where the names are matched.

I have one workbook with two worksheets. On the first worksheet I have a list of 12 names using 12 rows, say A1:A12 (this will never change), and on the second worksheet I have one column say Column A1:A6 with 6 names and in column C1:C6, I have the remaining 6 names. This is one team playing another team. In the next 12 columns, the first 6 columns (D1:J6) are scores ect for the team in column A, and the foloowing 6 columns (K1:O6) relate to the team named in column C.

What I want to do is use the formula which takes the name from the list on the first worksheet, matches it with the name in column A of the second worksheet, if it finds a match, then returns a value from column D:J, if it doesn't find a match in column A, looks in Column C, if it finds a match in column C returns a value from columns K:O. There will always be a match in either column A or C

Is this possible please?

Any help given is much appreciated

Kind Regards

Peter

C1:

=VLOOKUP(A1,Table1,ColIdx,0)

B1, the result cell:

=IF(ISNA(C1),VLOOKUP(A1,Table2,ColIdx,0),C1)

Table1 ---> A1:J6 on the second sheet.

Table2 ---> C1:O6 on the second sheet.
 

clares

Well-known Member
Joined
Mar 14, 2002
Messages
557
Hi All

[/quote]Let's say it finds the name in A1. What do you want to return from D:J? What is in those cells?

In other words, how do you identify what column to return from D1:J1?
If the name was found in A1, I would want to return the value that is in D1, If the Name was in C1, I would want to return the value in K1.

I would then use the same forumla to put in the next column on the first sheet, but if it found the name in A1, then I would want it to return the value in E1, and if it found the name in C1, I would want to return the value in L1.

The first sheet has 6 columns next to the names, and the second sheet has 6 columns for each of the team names.

Kind Regards

Peter
 

clares

Well-known Member
Joined
Mar 14, 2002
Messages
557

ADVERTISEMENT

Hi All

[/quote]Let's say it finds the name in A1. What do you want to return from D:J? What is in those cells?

In other words, how do you identify what column to return from D1:J1?
If the name was found in A1, I would want to return the value that is in D1, If the Name was in C1, I would want to return the value in K1.

I would then use the same forumla to put in the next column on the first sheet, but if it found the name in A1, then I would want it to return the value in E1, and if it found the name in C1, I would want to return the value in L1.

The first sheet has 6 columns next to the names, and the second sheet has 6 columns for each of the team names.

Kind Regards

Peter
 

clares

Well-known Member
Joined
Mar 14, 2002
Messages
557
Hi All

[/quote]Let's say it finds the name in A1. What do you want to return from D:J? What is in those cells?

In other words, how do you identify what column to return from D1:J1?
If the name was found in A1, I would want to return the value that is in D1, If the Name was in C1, I would want to return the value in K1.

I would then use the same forumla to put in the next column on the first sheet, but if it found the name in A1, then I would want it to return the value in E1, and if it found the name in C1, I would want to return the value in L1.

The first sheet has 6 columns next to the names, and the second sheet has 6 columns for each of the team names.

Kind Regards

Peter
 

clares

Well-known Member
Joined
Mar 14, 2002
Messages
557

ADVERTISEMENT

Hi All

[/quote]Let's say it finds the name in A1. What do you want to return from D:J? What is in those cells?

In other words, how do you identify what column to return from D1:J1?
If the name was found in A1, I would want to return the value that is in D1, If the Name was in C1, I would want to return the value in K1.

I would then use the same forumla to put in the next column on the first sheet, but if it found the name in A1, then I would want it to return the value in E1, and if it found the name in C1, I would want to return the value in L1.

The first sheet has 6 columns next to the names, and the second sheet has 6 columns for each of the team names.

Kind Regards

Peter
 

clares

Well-known Member
Joined
Mar 14, 2002
Messages
557
Hi All

[/quote]Let's say it finds the name in A1. What do you want to return from D:J? What is in those cells?

In other words, how do you identify what column to return from D1:J1?
If the name was found in A1, I would want to return the value that is in D1, If the Name was in C1, I would want to return the value in K1.

I would then use the same forumla to put in the next column on the first sheet, but if it found the name in A1, then I would want it to return the value in E1, and if it found the name in C1, I would want to return the value in L1.

The first sheet has 6 columns next to the names, and the second sheet has 6 columns for each of the team names.

Kind Regards

Peter
 

clares

Well-known Member
Joined
Mar 14, 2002
Messages
557
Hi All

[/quote]Let's say it finds the name in A1. What do you want to return from D:J? What is in those cells?

In other words, how do you identify what column to return from D1:J1?
If the name was found in A1, I would want to return the value that is in D1, If the Name was in C1, I would want to return the value in K1.

I would then use the same forumla to put in the next column on the first sheet, but if it found the name in A1, then I would want it to return the value in E1, and if it found the name in C1, I would want to return the value in L1.

The first sheet has 6 columns next to the names, and the second sheet has 6 columns for each of the team names.

Kind Regards

Peter
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
The you would use:
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$Q$6,4,FALSE)),VLOOKUP(A1,Sheet2!$C$1:$Q$6,10,FALSE),VLOOKUP(A1,Sheet2!$A$1:$Q$6,4,FALSE))

When ,4, represents D and
,10, represents L

In other words to get column E, change ,4, to ,5, etc
 

Watch MrExcel Video

Forum statistics

Threads
1,123,053
Messages
5,599,524
Members
414,315
Latest member
Yolanda5050

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