Find a column, then a row

  • Thread starter Thread starter Christian Davidsson
  • Start date Start date
C

Christian Davidsson

I've got a value in Sheet1!A1. Sheet1!B1 needs to lookup a value on Sheet2.
The problem is that it needs to find the value in Sheet1!A1 on Sheet2!A:R,
then get the value of row 125 of that column. How is this done? I've tried a
couple of formulas, but can't get it to work.

Also - on a similar note, how do I do if I want it to do a lookup to find a
row instead of telling Excel that it's on row 125?

Much appriciated if someone could help me out on both questions.


Thanks in advance,
Christian Davidsson
 
I've got a value in Sheet1!A1. Sheet1!B1 needs to lookup a value on Sheet2.
The problem is that it needs to find the value in Sheet1!A1 on Sheet2!A:R,
then get the value of row 125 of that column. How is this done? I've tried a
couple of formulas, but can't get it to work.

Also - on a similar note, how do I do if I want it to do a lookup to find a
row instead of telling Excel that it's on row 125?

Much appriciated if someone could help me out on both questions.


Thanks in advance,
Christian Davidsson
Click to expand...

Perhaps you could use the 'hlookup'-formula? This will work if you have got
the 'found value' in the first row of Sheet2. But if you have to search for
the 'to find value' in multiple rows and columns, then you will find this
very hard.

But have a look at the help for the 'HLOOKUP'-function.

CoRrRan
 
Now you're scaring me Corran Horn, great name btw (Star Wars baby!).

Anyway, I had JUST posted a subject wanting to do that very thing.

Corran said:
But if you have to search for the 'to find value' in multiple rows
and columns, then you will find this very hard.
Click to expand...

I am digging and, yes, finding this very hard, but I must ask why this
would be so difficult? Why wouldn't Excel have a nice little function
written for this?
 
Corran Horn said:
Perhaps you could use the 'hlookup'-formula? This will work if you have got
the 'found value' in the first row of Sheet2. But if you have to search for
the 'to find value' in multiple rows and columns, then you will find this
very hard.

But have a look at the help for the 'HLOOKUP'-function.

CoRrRan
Click to expand...

Thank you for your answer. It's possible to use the HLOOKUP, but Sheet1 had
to be redesigned a bit. On the sheet that is beeing looked up (Sheet2) I've
got ...

Col C, Row1 = Item ID
Col D, Row2 = Item name
..... ... ...
Col C, Row 125 = a text value
Col D, Row 125 = the value to look up

So I had to recreate Sheet1 so that it has the Item name as well, and base
the lookup on that.
=HLOOKUP($B2;'Sheet2'!$D:$R;125;FALSE).

But it works now, thanks again.

// Christian Davidsson
 
Back
Top