Search for value in Column A and return the value in Column B

[minismood]

Hello!

I have 21 sheets and in one sheet I want to search for a value, i.e
440011 in Column A, in the other 20 sheets and return the value from
Column B to the sheet where I put the formula.

I know it´s possible...

I used the formula =INDEX(b1:b20;MATCH(440011;a1:a20;0)) but I can´t
seem to make it work when I want to search through all the sheets, i.e.
=INDEX(Sheet1!b1:Sheet20!b20;MATCH(440011;Sheet1!a1:sheet20!a20;0))...

Please help me..

 

[Domenic]

Try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=VLOOKUP(440011,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&ROW(
INDIRECT("1:20"))&"!A1:A20"),440011)>0,0)&"!A1:B20"),2,0)

Note the following:

"Sheet"&ROW(INDIRECT("1:20")) refers to Sheet1:Sheet20

A1:A20 refers to the range for the first column of the lookup table

A1:B20 refers to the range for the lookup table

Change these references accordingly.

Hope this helps!

 

[minismood]

Thank you but I must ask a stupid question....

My actual references are:

u21:u33 and w21:w33 = u is where the project is i.e. 440011 and w is
where the value that I want is.

and the actual names of the sheets is from arb

Thank you again!

 

[Domenic]

In that case, try the following instead...

1) Enter a list of sheet names in a range of cells, let's say A1:A10

2) Then, use the following formula:

=VLOOKUP(440011,INDIRECT("'"&INDEX(A1:A10,MATCH(TRUE,COUNTIF(INDIRECT("'"
&A1:A10&"'!U21:U33"),440011)>0,0))&"'!U21:W33"),3,0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[minismood]

Okey...I tried it but maybe I don´t understand...doesn´t work so

But let me give you some more exact information.

My sheet has several sheets, about 35 I would believe, and I want to
search through the sheets which are named aa, bb, cc, dd, ee, ff, gg,
hh, ii, jj, kk, ll, mm, nn, oo, pp ,qq, rr, ss. In these sheets I want
to search for a project, i.e. 440011 in U21:U33 and then search for a
value in W21:W33. and then return the value in W21:W33 to a sheet where
I summarize all the projects, so I know how much time we spend on each
project.

Maybe you´ll give me the same answer as before

But hey man, I really appreciate your help!

 

[Domenic]

Yeah, the formula I gave you will do what you want. So let's take it a
step at a time. You say you have 35 sheets (more or less), so enter
your sheet names in a range of cells, let's say A1:A35. So A1 would
contain aa, A2 would contain bb, A3 would contain cc, and so on. Once
you've entered your sheet names, use the following formula...

=VLOOKUP(440011,INDIRECT("'"&INDEX(A1:A35,MATCH(TRUE,COUNTIF(INDIRECT("'"
&A1:A35&"'!U21:U33"),440011)>0,0))&"'!U21:W33"),3,0)

Note that you need to confirm the formula with CONTROL+SHIFT+ENTER, not
just ENTER. In other words, after typing the formula, press the CONTROL
and SHIFT keys down, then, while both keys are pressed down, press ENTER.

Does this help?