Looking for VLOOKUP Trick

shdawson

shdawson

Active Member
Joined
Jan 6, 2007
Messages
381
Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?


Thanks,
S
 
Sort by date Sort by votes
This is the sheet where the formula will live, in column A:
Excel Workbook
ABCDEFG
1MSPProject ManagerTowerWorkstreamSubject AreaClarity #
2ADWRoadmap0
3ADWRoadmap0
4ADWRoadmap0
5ADWRoadmap0
6xADWRoadmap0
7ADWRoadmap47644
8ADWRoadmap47644
9ADWRoadmap47644
10ADWRoadmap47646
11ADWRoadmap47647
12ADWRoadmap47649
Step 1 - Get Roadmap Entries
Excel 2003
This is the lookup sheet:
Excel Workbook
DEFGHI
1Project #Control #TowerWorkstreamProjectMSP
2801801.0ADWRoadmapEnterprise Data ServicesR
34764447644.0ADWTD RemodelApplication End StateY
Detail
Excel 2003


So, looking for a Y to go into A7, where A1-A6 has nothing to be found.


Thank You,
SHD
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
This works for me:

Code: 
=IF(ISNA(MATCH(1,IF(Detail!$F$2:$F$1000=D2,IF(Detail!$D$2:$D$1000=G2,1)),0)),"",INDEX(Detail!$I$2:$I$1000,MATCH(1,IF(Detail!$F$2:$F$1000=D2,IF(Detail!$D$2:$D$1000=G2,1)),0)))
Remember to commit it with CTRL+SHIFT+ENTER.

Note: This will only work properly if 'Tower' and 'Project #', in combination, represent a unique reference.

Matty
 
Last edited:
Upvote 0
Nice formula edit. :)

The formula still will not calculate on the worksheet. It shows as the formula in the cell, not even #N/A in the cell.

So, when this happens, is the formula the problem or is there something else that is wrong that is preventing the formula from evaluating?


Thanks,
SHD
 
Upvote 0
Edit to the formula, from another person.

Code: 
=IF(SUM((D2=Detail!$F2:F$1000)*(G2=Detail!$D2:$D$1000))=0,"",INDEX(Detail!$I$2:$I$1000,SUM((D2=Detail!$F2:F$1000)*(G2=Detail!$D2:$D$1000)*(ROW(Detail$I$1)))))


Same result, the formula is not processing. I see the formula in the cell.

Also, the curly brakets are not taking via C-S-E.

It could be how the external workbook is being referenced. I have these workbooks linked through Menu/Edit/Links.

Should the naming of the workbook being referenced change? Hard brackets or perhaps quotation marks of some kind?

Finally, not sure about the $I$1 part.


Thanks,
SHD
 
Upvote 0
Look at this sheets:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>MSP</TD><TD>Project Manager</TD><TD style="TEXT-ALIGN: right"></TD><TD>Tower</TD><TD>Workstream</TD><TD>Subject Area</TD><TD>Clarity #</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>=SE(SOMA((D2=Detail!$F$2:$F$3)</TD><TD></TD><TD></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47646</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47647</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47649</TD></TR></TBODY></TABLE>Master-Problem


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>MSP</TD><TD>Project Manager</TD><TD style="TEXT-ALIGN: right"></TD><TD>Tower</TD><TD>Workstream</TD><TD>Subject Area</TD><TD>Clarity #</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Y</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Y</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Y</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47646</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47647</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>ADW</TD><TD>Roadmap</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47649</TD></TR></TBODY></TABLE>Master-OK


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A2</TH><TD style="TEXT-ALIGN: left">{=IF(SUM((D2=Detail!$F$2:$F$3)*(G2=Detail!$D$2:$D$3))=0,"",INDEX(Detail!$I$2:$I$3,SUM((D2=Detail!$F$2:$F$3)*(G2=Detail!$D$2:$D$3)*(ROW(Detail!$I$2:$I$3)-ROW(Detail!$I$1)))))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Project #</TD><TD>Control #</TD><TD>Tower</TD><TD>Workstream</TD><TD>Project</TD><TD>MSP</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">801</TD><TD style="TEXT-ALIGN: right">801.0</TD><TD>ADW</TD><TD>Roadmap</TD><TD>Enterprise Data Services</TD><TD>R</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">47644</TD><TD style="TEXT-ALIGN: right">47644.0</TD><TD>ADW</TD><TD>TD Remodel</TD><TD>Application End State</TD><TD>Y</TD></TR></TBODY></TABLE>Detail

You must delete the column A and then add a new column A in the sheet Master-Problem.

How to:

Select the entire column A (giving a click in the header) and clicking with the right mouse button on the selection and choose Delete.

Then, select the entire new column A (former column B) and click the right mouse button again on the selection and choose Insert.

Markmzz
 
Upvote 0
I'm still having problems with the N/A result :mad: and am really hoping someone can help me out.

I've tried the suggestions below, checking to make sure the cells are formatted as numbers, checking for any hidden spaces before or after the number and all have not worked.

Since the HTML Maker add-in is currently unavailable, I can email my workbook to whoever is willing to help, or is there another way to post my file?

Any help would be GREATLY appreciated! :)


DonkeyOte said:
You mentioned VLOOKUP tricks - demo'd by Barry Houdini the other day

Code: 
=VLOOKUP(A2,CHOOSE({1,2},'Tasks Report'!X$2:X$10000,'Tasks Report'!L$2:L$10000),2,0)

In terms of your error - the MATCH should be exact I suspect:

Code: 
=INDEX('Tasks Report'!L$2:L$10000,MATCH(A2,'Tasks Report'!X$2:X$10000,0))
Click to expand...
 
Upvote 0
Right.

The problem is this referencing out to the linked workbook. I enter your formula, Mark, and I am prompted to point to the workbook. I do so. It double enters

Current Formula:
Code: 
=IF(SUM((C2=[Detail]Detail!$F$2:$F$1000)*(F2=[Detail]Detail!$D$2:$D$1000))=0,"",INDEX([Detail]Detail!$I$2:$I$1000,SUM((C2=[Detail]Detail!$F$2:$F$1000)*(F2=[Detail]Detail!$D$2:$D$3)*(ROW([Detail]Detail!$I$2:$I$1000)-ROW([Detail]Detail!$I$1)))))

I expanded the rows to 1000, in order to get all of the lookup table.

I tried to delete the Detail, both in parenthesis and with the exclamation point. I am still prompted to point to the workbook.

The end result is #N/A for all cells. It has to be how the external is being referenced, wtih the double statment of Detail.

I checked the link between the workbooks. The lookup table is fine, but it is trying to refernce Detail as a different file name. This is expected, with the double entry of Detail.

The curly brackets are in place, via C-S-E. HORRAY!

When I try to show calcualtion steps, Excel crashes. Probalby the double reference again.

So, if this referencing the linked worksheet can be resolved, I would say this is most likely done. Perhaps linking the worksheets is not the best approach...?


Thanks,
SHD
 
Upvote 0
Shdawson,

In my example, all the sheets (Master-Problem, Master-OK and Detail) are in the same Workbook (SHD2.xls).

And in yours? Give me more detail examples.

Markmzz
 
Upvote 0
OK, simple is better. Diagnose the problem, then seperate the worksheets as necessary later.

Copied the worksheet to the same workbook. Changed the worksheet reference.

Current Formula:
Code: 
=IF(SUM((C2=Detail!$F$2:$F$1000)*(F2=Detail!$D$2:$D$1000))=0,"",INDEX(Detail!$I$2:$I$1000,SUM((C2=Detail!$F$2:$F$1000)*(F2=Detail!$D$2:$D$3)*(ROW(Detail!$I$2:$I$1000)-ROW(Detail!$I$1)))))


with curly brackets ala C-S-E.

I had this worksheet in a seperate workbook, and pointed to the lookup table. Excel then Linked the workbooks.

Still getting #N/A

When I right-click on a cell to Show Calcuation steps, Excel crashes...sends error report to Microsoft...and restarts Excel.

Perhaps I need to change the oil in this computer. LOL.


Hmmmmm......
S
 
Upvote 0
Current view of worksheet that needs the formula:
Excel Workbook
ABCDEF
1MSPProject ManagerTowerWorkstreamSubject AreaClarity #
2#N/AADWRoadmap0
3#N/AADWRoadmap0
4#N/AADWRoadmap0
5#N/AADWRoadmap0
6#N/AADWRoadmap0
7#N/AADWRoadmap47644
8#N/AADWRoadmap47644
9#N/AADWRoadmap47644
10#N/AADWRoadmap47646
11#N/AADWRoadmap47647
12#N/AADWRoadmap47649
13#N/AADWRoadmap47650
14#N/AADWRoadmap47669
15#N/AADWRoadmap47679
16#N/AADWRoadmap47679
17#N/AADWRoadmap47679
18#N/AADWRoadmap47680
19#N/AADWRoadmap47682
20#N/AADWRoadmap48880
Step 1 - All Roadmap Entries
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.


Current view of worksheet that supplies that looked up data:
Excel Workbook
DEFGHI
1Project #Control #TowerWorkstreamProjectMSP
2801801.0ADWRoadmapEnterprise Data ServicesR
34764447644.0ADWTD RemodelApplication End StateY
Detail
Excel 2003
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
=IF Formula Question
Replies
5
Views
315
Fluff
Fluff
P
  • Question
VLOOKUP bottom to top
Replies
1
Views
213
awoohaw
awoohaw
M
  • Question
VLOOKUP help - return value if two partial matches are met (I think??)
Replies
5
Views
386
MountainFog
M
JTL9161
  • Question
MATCH in Conditional Formatting
Replies
2
Views
421
Peter_SSs
Peter_SSs
A
  • Solved
Formulas and relative pointing
2
Replies
15
Views
594
atrainwreck
A

Forum statistics

Threads
1,216,109
Messages
6,128,883
Members
449,477
Latest member
panjongshing

Share this page

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