Lookup With 2 Index Directly

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,077
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi all...

how to make lookup formula with 2 index (id_1 & id_2) like this :

id_1tipeid_2
4010106010Buliding A1
4010202007Buliding B1
4010205001Permanent49
4010202004Resident1
4010205001Houses46
4010205001Houses38
4010205001Houses42
4010205001Houses51
4010205001Houses55
4010205001Houses59
4010205001Houses63
given (id_1)given (id_2)expected result
40102020071Buliding B
401020500146Houses
etc…

<colgroup><col><col><col></colgroup><tbody>
</tbody>
any help greathly appreciated...

m.susanto
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Selamat siang :)

One way is to join the two fields and make a new key/code to lookup against. Such as =A2&"|"&C2
This will give text keys like
4010106010|1 for building A
4010202007|1 for building B
4010205001|49 for Permanent
etc

Then you can lookup a text string made the same way in the table where you want the results. Would that work for you?

(Another way which is more complex is to use an UPDATE query. This uses SQL to define what is being done & can be executed manually from the worksheet (a bit like refreshing a pivot table) or, once set up, more easily via VBA.)

Selamat, Fazza
 
Upvote 0
thank Fazza..how to make it formula...
how to make =A2&"|"&C2,...i can't find mark red in my keyboard
 
Upvote 0
Kembali. Just use another character. Preferably that won't be in the data already. Such as ~ or # or & or + or - or _
Or, you could even leave it out entirely. So just have =A2&C2
 
Upvote 0
i have trying =VLOOKUP(D18&"ӏ"&E18;D3:F13;2;FALSE) but not working #N/A
 
Upvote 0
Only guessing, now.
One problem, sometimes, is extra spaces in names. Such as instead of ID "1230010" the cell entry is "1230010 ". So check that there are no extra spaces or similar.

What is in these ranges?
D18
E18
D3:F13
 
Upvote 0
can you provide the data from all the relevant cells? What is in these ranges?

D18
E18
D3:F13
 
Upvote 0
Tolong, best to show what is in the exact ranges used in the formula to help understand why the formula is not working.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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