Lookup values in multiple cells

NSegna

New Member
Joined
May 30, 2019
Messages
11
Hi,

I was wondering if I is possible for excel to read data found in 3 cells to find a number associated with those 3 cells on another sheet and insert that number back on the first sheet.


[TABLE="width: 180"]
<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" span="3"> <tbody>[TR]
[TD="width: 80, bgcolor: transparent"][TABLE="width: 240"]
<colgroup><col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" span="4"> <tbody>[TR]
[TD="width: 80, bgcolor: transparent"]11001[/TD]
[TD="width: 80, bgcolor: transparent"]11002[/TD]
[TD="width: 80, bgcolor: transparent"]11003[/TD]
[TD="width: 80, bgcolor: transparent"]11003[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BVLD TCE-BL[/TD]
[TD="bgcolor: transparent"]BVLD TCE-BL[/TD]
[TD="bgcolor: transparent"]BVLD TCE-BL[/TD]
[TD="bgcolor: transparent"]BVLD TCE-BL[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]HORSESHOE[/TD]
[TD="bgcolor: transparent"]DOWNTOWN
[/TD]
[TD="bgcolor: transparent"]HORSESHOE[/TD]
[TD="bgcolor: transparent"]REACTIVE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]XXXXXXX[/TD]
[TD="bgcolor: transparent"]XXXXXXX[/TD]
[TD="bgcolor: transparent"]XXXXXXX
[/TD]
[TD="bgcolor: transparent"]XXXXXXX

[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][TABLE="width: 657"]
<colgroup><col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3328;"> <col width="574" style="width: 431pt; mso-width-source: userset; mso-width-alt: 20992;"> <col width="154" style="width: 116pt; mso-width-source: userset; mso-width-alt: 5632;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <tbody>[TR]
[TD="width: 91, bgcolor: transparent"]11037 [/TD]
[TD="width: 574, bgcolor: transparent"]BLVD TCE-BL[/TD]
[TD="width: 154, bgcolor: transparent"]HORSESHOE[/TD]
[TD="width: 56, bgcolor: transparent, align: right"]3017518[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11041 [/TD]
[TD="width: 574, bgcolor: transparent"]BLVD TCE-BL[/TD]
[TD="bgcolor: transparent"]HORSESHOE[/TD]
[TD="bgcolor: transparent, align: right"]3002502[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11045 [/TD]
[TD="width: 574, bgcolor: transparent"]BLVD TCE-BL[/TD]
[TD="bgcolor: transparent"]HORSESHOE[/TD]
[TD="bgcolor: transparent, align: right"]3017591[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11001 [/TD]
[TD="width: 574, bgcolor: transparent"]BLVD TCE-BL[/TD]
[TD="bgcolor: transparent"]DOWNTOWN[/TD]
[TD="bgcolor: transparent, align: right"]3012453[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11002 [/TD]
[TD="width: 574, bgcolor: transparent"]BLVD TCE-BL[/TD]
[TD="bgcolor: transparent"]DOWNTOWN[/TD]
[TD="bgcolor: transparent, align: right"]3007534[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11003 [/TD]
[TD="width: 574, bgcolor: transparent"]BLVD TCE-BL[/TD]
[TD="bgcolor: transparent"]DOWNTOWN[/TD]
[TD="bgcolor: transparent, align: right"]3010712[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11004 [/TD]
[TD="width: 574, bgcolor: transparent"]BLVD TCE-BL[/TD]
[TD="bgcolor: transparent"]DOWNTOWN[/TD]
[TD="bgcolor: transparent, align: right"]3002970[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]For example, I would like excel to read 11002, BVLD TCE-BL, and DOWNTOWN and give me the number 3007534 where the X's are. An IF statement would not work in this situation as there are 21000+ rows in the file.

Thank you for the help
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Welcome to the forum.

I think this might help. Copy the formula A4 across. Depending on your version of Excel, the formula in A4 might need to be invoked with Ctrl+Shift+Enter.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Book1
ABCD
111001110021100311003
2BVLD TCE-BLBVLD TCE-BLBVLD TCE-BLBVLD TCE-BL
3HORSESHOEDOWNTOWNHORSESHOEREACTIVE
4not found3007534not foundnot found
5
611037BVLD TCE-BLHORSESHOE3017518
711041BVLD TCE-BLHORSESHOE3002502
811045BVLD TCE-BLHORSESHOE3017591
911001BVLD TCE-BLDOWNTOWN3012453
1011002BVLD TCE-BLDOWNTOWN3007534
1111003BVLD TCE-BLDOWNTOWN3010712
1211004BVLD TCE-BLDOWNTOWN3002970
Sheet35
Cell Formulas
RangeFormula
A4=IFNA(INDEX($D$6:$D$12,MATCH(A1&A2&A3,$A$6:$A$12&$B$6:$B$12&$C$6:$C$12,0)),"not found")
[/FONT]
 
Upvote 0
Thank you for your response,
Unfortunately, the formula gave me a #VALUE ! Error when I entered it on my spreadsheet. I then tried to recreate the sample you sent me and got the same error message on that too.
 
Upvote 0
Did you confirm it with Ctrl Shift Enter?
If so you should see the formula wrapped in {}
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,348
Members
452,638
Latest member
Oluwabukunmi

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