# How to do a Complex VLOOKUP

#### Mstg007

##### Active Member
 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 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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### StephenCrump

##### MrExcel MVP
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)

#### Mstg007

##### Active Member
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.

#### StephenCrump

##### MrExcel MVP
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.

#### Mstg007

##### Active Member
Boom. Works great! and Thank you

#### Mstg007

##### Active Member
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?

#### StephenCrump

##### MrExcel MVP
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?

#### Mstg007

##### Active Member
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

Replies
1
Views
226
Replies
1
Views
242
Replies
1
Views
187
Replies
1
Views
649
Replies
1
Views
206

### Forum statistics

1,195,625
Messages
6,010,754
Members
441,568
Latest member
abbyabby ### 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.

### Which adblocker are you using?    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

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