GoogleSheet - Trying to use nested MATCH inside VLOOKUP

jonathan92591

Board Regular
Joined
Oct 27, 2011
Messages
65
Hi All - I am trying to use "Match" inside a "Vlookup" for the Index input on the "Vlookup" formula. I am trying to pull "Estimated Hours" from one spreadsheet to another spreadsheet using "Work Order ID" as the ID>

Work Order Log to Work Order Log - Rollup

Spreadsheets: https://drive.google.com/open?id=1kFYjRcMAKDx5eJHYdQAqFnyVs6m8O1pu

Attempt without nested "Match":

=VLOOKUP($A2,IMPORTRANGE("1c-Mu40dSREGNUeuY5DjIftspdT9QG8O5zf4q1W2QxC4","Details!A2:P502"),14,0)

Attempt WITH nested "Match":

=VLOOKUP($A3,IMPORTRANGE("1c-Mu40dSREGNUeuY5DjIftspdT9QG8O5zf4q1W2QxC4","Details!A2:P502"),MATCH("ESTIMATED HOURS",IMPORTRANGE("1c-Mu40dSREGNUeuY5DjIftspdT9QG8O5zf4q1W2QxC4","Details!A1:A50"),0),0)

Thank you for your support in advance. - JT
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Correction to formulas:

Attempt without nested "Match": Does Work

=VLOOKUP($A2,IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A2:B7"),2,0)

Attempt WITH nested "Match": Doesn't Work

=VLOOKUP($A3,IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A2:P502"),MATCH("ESTIMATED HOURS",IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A1:A50"),0),0)
 
Upvote 0
My friend solved it! The formula was:

=VLOOKUP($A3,IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A2:B7"),MATCH("Estimated Hours",IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!1:1"),0),0)

I was the last part of the match, I was typing A but it was 1.

Thank you all!
 
Upvote 0
See if one of the following would work?

=VLOOKUP($A3,IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A:P"),MATCH("ESTIMATED HOURS",IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A1:P1"),0),0)

=VLOOKUP($A3,IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A2:P502"),MATCH("ESTIMATED HOURS",INDEX(IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A:P"),1,0),0),0)
 
Upvote 0
See if one of the following would work?

=VLOOKUP($A3,IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A:P"),MATCH("ESTIMATED HOURS",IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A1:P1"),0),0)

=VLOOKUP($A3,IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A2:P502"),MATCH("ESTIMATED HOURS",INDEX(IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A:P"),1,0),0),0)

Details!A:P change the A and the P to 1

So
Details!1:1"

That ended up working out for us.
 
Upvote 0
So you did not check out the suggestions.

Ok. I tried the two you suggested. They did not work.

This ended up working:

=VLOOKUP($A3,IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!A2:B7"),MATCH("Estimated Hours",IMPORTRANGE("1TVAEdKxfPjIqjPDXqEpRVLoqDQZJP9658g4iwNSfzD8","Details!1:1"),0),0)
 
Upvote 0
They should work, but never mind.

You have a solution. Try to add a column in front of the current column of the data range. Does the solution you opted for still work?
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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