multiple lookups

[kiran5]

Hi everyone,
My first column(Acolum) has emp numbers and next to it has some numbers in B, C, D, E, F, G, H, I &JColumns.
Now if I place the emp number in sheet 2(a1 cell), my B2 cell should search the emp number in sheet a column and get the last updated column info for that particular emp number.
example
In A column
11111 - a1
22222 -a2

B1 - 1000
C1 - 2000
G1 - 4000
H1 - 5000
J1 - 10000
B2 - 2000
F2 - 5000

Here if I give 11111 the output should show as 10000 & if I give 22222 then it should show 5000.
If any error should show blank. We stii use excel 2003 only.. so pls assist a formula for this...

 

[SirJB7]

Hi, kiran5!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look at the uploaded file. In 1st worksheet there's a range of 2 rows x 10 columns with a title row, with random data for values in column A 11111 and 22222. In 2nd worksheet there's a title row, and below a range of 3 rows x 10 columns; in column A you'll find 11111, 22222 & 33333. Play a bit changing the values in column A and see what happens.

Formula at B2:
=BUSCARV($A2;Hoja1!$A:$J;COLUMNA();FALSO) -----> in english: =VLOOKUP($A2,Hoja1!$A:$J,COLUMN(),FALSE)
... copy down and across as required (in the example thru J3.

Just advise if any issue.

Regards!

 

[kchiba]

Hi Kiran

try this

insert a column B and enter this formula in B1

'=INDEX(C1:G1,1,MATCH(TRUE,INDEX(ISBLANK(C1:G1),0,0),0)-1)

cheers kanti

 

[kiran5]

Hi,
but I need only the last updated column output in hoja2 sheet.
example:
Hoja2A1 - 11111
Hoja2b2 should show the lookup of hoja1 a to j column and give the j column details if there is no value in j column then should show I column details else h column n so on till b column.


My hoja2 sheet will have only A n B columns

 

[SirJB7]

Hi, kiran5!

I apologize, I misread your requirement. Give a look at the fixed and updated file.

Data in worksheet 1:
A1, B1, C1, G1, H1, J1
A2, B2, F2
with the values you posted.

Data in worksheet 2:
A1: 11111
A2: 22222
A3: 33333

Formula at B1:
=SI(ESNOD(COINCIDIR(A1;Hoja1!$A:$A;0));"";INDICE(Hoja1!$A:$J;COINCIDIR(A1;Hoja1!$A:$A;0);COINCIDIR(BUSCAR(10^99;Hoja1!1:1);Hoja1!1:1;0))) -----> in english: =IF(ISNA(MATCH(A1,Hoja1!$A:$A,0)),"",INDEX(Hoja1!$A:$J,MATCH(A1,Hoja1!$A:$A,0),MATCH(LOOKUP(10^99,Hoja1!1:1),Hoja1!1:1,0)))

Copied down as required, thru B3.

Displayed:
B1: 10000
B2: 5000
B3: blank

Hope I didn't missed the shot now.

Regards!

 

[kiran5]

Have tried it but the formula works only when A column details r in the same order in both hoja1 & hoja2 sheets.

My emp numbers in hoja2 might or might not exists in hoja1sheet. So in this case this formula doestnt show accurate output.

Apologies for too much of posts on this.

 

[SirJB7]

Hi, kiran5!
Needed more Carlsberg, I guess. 3rd and last.
Regards!

 

[Somendra Misra]

Hi kiran5,

Although @SirJB7 had answered your question, I thought of giving you another formula option for the same.

Have a look of attached file.

I had use dynamic name range for employee number & than use this name range to generate a drop down validation list on sheet 2 and extracted last colunm with data for that employee number.
I had use below formula to get the data.

IF(A2="","",LOOKUP(9.99E+100,INDIRECT("Sheet1!"&MATCH(A2,Emp_Code,0)+1&":"&MATCH(A2,Emp_Code,0)+1)))

Just advise if you have any issue in this.

Regards!

 

[kiran5]

that wz amazing SirJB7. Couldn't reply morning. Tanks a lot for this.

Thanks to U too Somendra.

 

[Somendra Misra]

@kiran5

Glad you solved it.

Regards!

 

[SirJB7]

Hi, kiran5!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!