Lookup With 2 Index Directly

[muhammad susanto]

hi all...

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

[TABLE="width: 296"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]id_1[/TD]
[TD]tipe[/TD]
[TD]id_2[/TD]
[/TR]
[TR]
[TD="align: right"]4010106010[/TD]
[TD]Buliding A[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4010202007[/TD]
[TD]Buliding B[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4010205001[/TD]
[TD]Permanent[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD="align: right"]4010202004[/TD]
[TD]Resident[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4010205001[/TD]
[TD]Houses[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD="align: right"]4010205001[/TD]
[TD]Houses[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD="align: right"]4010205001[/TD]
[TD]Houses[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]4010205001[/TD]
[TD]Houses[/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD="align: right"]4010205001[/TD]
[TD]Houses[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD="align: right"]4010205001[/TD]
[TD]Houses[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD="align: right"]4010205001[/TD]
[TD]Houses[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]given (id_1)[/TD]
[TD]given (id_2)[/TD]
[TD]expected result[/TD]
[/TR]
[TR]
[TD="align: right"]4010202007[/TD]
[TD="align: right"]1[/TD]
[TD]Buliding B[/TD]
[/TR]
[TR]
[TD="align: right"]4010205001[/TD]
[TD="align: right"]46[/TD]
[TD]Houses[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]etc…[/TD]
[/TR]
</tbody>[/TABLE]
any help greathly appreciated...

m.susanto

 

[Fazza]

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

 

[muhammad susanto]

thank Fazza..how to make it formula...
how to make =A2&"|"&C2,...i can't find mark red in my keyboard

 

[Fazza]

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

 

[muhammad susanto]

i have trying =VLOOKUP(D18&"ӏ"&E18;D3:F13;2;FALSE) but not working #N/A

 

[Fazza]

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

 

[muhammad susanto]

i'm still not understanding..but no extra spaces...

 

[Fazza]

can you provide the data from all the relevant cells? What is in these ranges?

D18
E18
D3:F13

 

[muhammad susanto]

note :

i don't use array function...maybe someone would help me...

 

[Fazza]

Tolong, best to show what is in the exact ranges used in the formula to help understand why the formula is not working.