How to do a Complex VLOOKUP

Mstg007

Active Member
Joined
Dec 30, 2013
Messages
383
Sheet1!Column A
Sheet1!Column B
Sheet1!Column C
Sheet3!Column AF
AF23 = "line"
AG23 = "time"
AH23 = "part"
Cell A1 = "Line"
B1 = 1
C1 = 1

AF24 = 1
AG24 = 10.0
AH24 = Box
Cell A2 = "Time"
B2 = 5.0
C2 = 10.0
AF25 = 2
AG25 = 9
AH25 = Box
Cell A3 = "Rise"
B3 = 1.5
C3 = 1.5
AF26 = 3
AG26 = 8.5
AH26 = Circle
Cell A4 = "Part"
B4 = Circle
C4 = Box
AF27 = 4
AG27 = 4.5
AH27 = Circle
Cell A5 = "Station"
B5 = 100
C5 = 100
Cell A6 = "Line"
B6 = 2
C6 = 2
Cell A7 = "Time"
B7 = 6.0
C7 = 9
Cell A8 = "Rise"
B8 = 1.25
C8 = 1.25
Cell A9 = "Part"
B9 = Circle
C9 = Circle
Cell A10 = "Station"
B10 = 121
C10 = 121
Cell A11 = "Line"
B11 = 3
C11 = 3
Cell A12 = "Time"
B12 = 3.5
C12 = 8.5
Cell A13 = "Rise"
B13 = 2.5
C13 = 2.5
Cell A14 = "Part"
B14 = Box
C14 = Circle
Cell A15 = "Station"
B15 = 136
C15 = 136
Cell A16 = "Line"
B16 = 4
C16 = 4
Cell A17 = "Time"
B17 = 6.5
C17 = 4.5
Cell A18 = "Rise"
B18 = 2
C18 = 2
Cell A19 = "Part"
B19 = Circle
C19 = Circle
Cell A20 = "Station"
B20 = 199
C20 = 199

<tbody>
</tbody>

I know I already made a post about this. Im Sorry.

I am not sure if I am trying to do a VLOOKUP or what, I hope the below table might help with my question. I have values on sheet1 column b that might change in column c based upon the results of sheet3. The results in sheet3 need be populated in sheet1 column c. If there is not a result from sheet3 then sheet1 column B result will be used in column c.

I hope that makes some sense
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
If I'm interpreting this correctly (?) then:

C2 = 10.0 because this is the first occurrence of "Time" and 10.0 is in the first row of the lookup table under "Time"

C7 = 9 because this is the second occurrence of "Time" and 9 is in the second row of the lookup table under "Time" .. etc

(Presumably C9 should be "Box" rather than "Circle", because the first two lines in the lookup table under "part" are both equal to "Box"?)

Try this formula in C1, then copy down Column C:

=IFERROR(INDEX($AF$23:$AH$27,1+COUNTIF(A1:A$1,A1),MATCH(A1,AF$23:AH$23,0)),B1)
 
Upvote 0
The thought is to have the vlookup search relative to the A1 "Line 1" and end the search on A5 "Station". As you drag down the formula it would then find repeat the search from A6 "Line 2 ectc.
 
Upvote 0
The thought is to have the vlookup search relative to the A1 "Line 1" and end the search on A5 "Station". As you drag down the formula it would then find repeat the search from A6 "Line 2 ectc.

I think we're saying the same thing in different ways?

Have you tried the formula? It replicates your results in C1:C20 with the one exception mentioned above - my C9 is "Box" because in the lookup table in AF23:AH27, the second line under the heading "Part" is "Box" (not "Circle").

Does this do what you wanted? If not, please provide more detailed steps of what you're trying to achieve, otherwise I'm lost.
 
Upvote 0
If I'm interpreting this correctly (?) then:

C2 = 10.0 because this is the first occurrence of "Time" and 10.0 is in the first row of the lookup table under "Time"

C7 = 9 because this is the second occurrence of "Time" and 9 is in the second row of the lookup table under "Time" .. etc

(Presumably C9 should be "Box" rather than "Circle", because the first two lines in the lookup table under "part" are both equal to "Box"?)

Try this formula in C1, then copy down Column C:

=IFERROR(INDEX($AF$23:$AH$27,1+COUNTIF(A1:A$1,A1),MATCH(A1,AF$23:AH$23,0)),B1)

How can I get the ($AF$23:$AH$27) and (AF$23:AH$23) to reference to sheet3?
 
Upvote 0
Simply change $AF$23:$AH$27 to Sheet3!$AF$23:$AH$27 etc.

For possible future reference, now try changing the name of Sheet3 to My Sheet3 (i.e. with a space in the middle). See how the reference in the formula needs to change when there are spaces in a sheet name?
 
Upvote 0
Simply change $AF$23:$AH$27 to Sheet3!$AF$23:$AH$27 etc.

For possible future reference, now try changing the name of Sheet3 to My Sheet3 (i.e. with a space in the middle). See how the reference in the formula needs to change when there are spaces in a sheet name?


I do. I though it had to be like this:

$AF$23:$AH$27 to 'Sheet3'!$AF$23:'Shee3'!$AH$27
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top