Help with Vlookup to report 2nd 3rd 4th etc Matches

Marsman

Board Regular
Joined
May 13, 2013
Messages
56
I have been reading post after post and cannot quite figure this out.

I need to be able to do a Vlookup without changing any data as "Helper Columns" etc.

As with the following example "Sheet 1" is where is am trying to have the info populate and "sheet 2" is the Data set.

Obviously when I do "Vlookup" it is finding the information for the 1st record and pasting it into the follwoing record.

The only common column would ever be Column A. the Data in Columns B and C would change consistantly.


Sheet 1
ABC
1P20LPIValue needed
2P20PFValue needed
3P20SCAValue needed
4p19LPI
Value needed​
5P20FC
Value needed​
6P16SCA
Value needed​
7P19DS FEC
Value needed​

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>


Sheet 2


ABC
1P20LPIDS RX
2P20PFUS
3P20SCAUS TX
4p19LPIDS RX
5P20FCNR
6P16SCAUS RX
7P19DS FECDS

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Marsman

Board Regular
Joined
May 13, 2013
Messages
56
The value Needed is what ever is in the "C" Column for the reference from "A".

ABC
1P20LPI'Sheet 2' "First Record for "P20"
2P20PF'Sheet 2' "Second Record for "P20"
3P20SCA'Sheet 2' "Third Record for "P20"
4p19LPIFILLER EXAMPLE
5P20FC'Sheet 2' "Fourth Record for "P20"
6P16SCAFILLER EXAMPLE
7P19DS FECFILLER EXAMPLE

<tbody>
</tbody>

<tbody>
</tbody>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
The value Needed is what ever is in the "C" Column for the reference from "A".

ABC
1P20LPI'Sheet 2' "First Record for "P20"
2P20PF'Sheet 2' "Second Record for "P20"
3P20SCA'Sheet 2' "Third Record for "P20"
4p19LPIFILLER EXAMPLE
5P20FC'Sheet 2' "Fourth Record for "P20"
6P16SCAFILLER EXAMPLE
7P19DS FECFILLER EXAMPLE

<tbody>
</tbody>

<tbody>
</tbody>

I don't understand your reply. What is: 'Sheet 2' "First Record for "P20"?
 

Marsman

Board Regular
Joined
May 13, 2013
Messages
56

ADVERTISEMENT

I don't understand your reply. What is: 'Sheet 2' "First Record for "P20"?

"Sheet 1" is where I am placing the Vlookup formula in Cell "C1" referencing cell "A1" (P20)

"Sheet 2" is the data I am getting the table Reference from, which have multiple records of the P20 in "Sheet 1" Cell "A1"

I am not able to figure out how to get the formula or any formula to be able to input into column C of "Sheet 1" to be able to report the different data, BEYOND the First Record, from "sheet 2"

for this example.... =vlookup(a1,'sheet 2'!$a$1:$c$7,3,false)


Sheet 1
ABC
1P20LPI=vlookup(a1,'sheet 2'!$a$1:$c$7,3,false)
2P20PF=vlookup(a2,'sheet 2'!$a$1:$c$7,3,false)
3P20SCA=vlookup(a3,'sheet 2'!$a$1:$c$7,3,false)
4p19LPI=vlookup(a4,'sheet 2'!$a$1:$c$7,3,false)
5P20FC=vlookup(a5,'sheet 2'!$a$1:$c$7,3,false)
6P16SCA=vlookup(a6,'sheet 2'!$a$1:$c$7,3,false)
7P19DS FEC=vlookup(a7,'sheet 2'!$a$1:$c$7,3,false)

<tbody>
</tbody>


<tbody>
</tbody>


<tbody>
</tbody>
 

gorem

Board Regular
Joined
Sep 28, 2013
Messages
188
Can column B serve as another lookup criteria from sheet2 or do the data not work that way? If so, you could just use a 2-criteria vlookup array.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

"Sheet 1" is where I am placing the Vlookup formula in Cell "C1" referencing cell "A1" (P20)

"Sheet 2" is the data I am getting the table Reference from, which have multiple records of the P20 in "Sheet 1" Cell "A1"

I am not able to figure out how to get the formula or any formula to be able to input into column C of "Sheet 1" to be able to report the different data, BEYOND the First Record, from "sheet 2"

for this example.... =vlookup(a1,'sheet 2'!$a$1:$c$7,3,false)


Sheet 1
ABC
1P20LPI=vlookup(a1,'sheet 2'!$a$1:$c$7,3,false)
2P20PF=vlookup(a2,'sheet 2'!$a$1:$c$7,3,false)
3P20SCA=vlookup(a3,'sheet 2'!$a$1:$c$7,3,false)
4p19LPI=vlookup(a4,'sheet 2'!$a$1:$c$7,3,false)
5P20FC=vlookup(a5,'sheet 2'!$a$1:$c$7,3,false)
6P16SCA=vlookup(a6,'sheet 2'!$a$1:$c$7,3,false)
7P19DS FEC=vlookup(a7,'sheet 2'!$a$1:$c$7,3,false)

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

Should they be: DS RX and US?
 

Marsman

Board Regular
Joined
May 13, 2013
Messages
56
Should they be: DS RX and US?


the data is kind of irrelevant that is column C on "Sheet 2", unless you are trying to elude to the Vlookup will need 2 types of criteria in order for it to work.

I "simply" need to be able to have the table show the first record then look for a 2nd record.

So the vlookup would find the First "P20" and report what is in the adjacent column of the same row, than find the Second "P20" and that info, so forth so on.
 

Marsman

Board Regular
Joined
May 13, 2013
Messages
56
Can column B serve as another lookup criteria from sheet2 or do the data not work that way? If so, you could just use a 2-criteria vlookup array.


No, unfortunately the data in the second sheet is not always the same in the 2nd and 3rd columns. The only consistent data that "could" be the same is in the first column.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
the data is kind of irrelevant that is column C on "Sheet 2", unless you are trying to elude to the Vlookup will need 2 types of criteria in order for it to work.

I "simply" need to be able to have the table show the first record then look for a 2nd record.

So the vlookup would find the First "P20" and report what is in the adjacent column of the same row, than find the Second "P20" and that info, so forth so on.

And you are decided not to affirm whether they are DS RX and US for the first two occurrences of P20...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

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