Vlookup Excel help

Whoop · 14 Sep 2007 at 11:52

Hi all,

I'm terrible at Excel and I wondered if the following is possible -

I need to search column A for the number 1 and then the word INVOICES directly underneath it. And then read back the result from column G.

My main problem is obviously the word INVOICES is repeated and sometimes not there at all so it somehow needs to look for number 1, check a few squares down and then look over. Or do nothing at all.

Is any of this possible?

Thanks in advance to any Excel gods

Whoop

div0 · 14 Sep 2007 at 12:29

good solution below

div0 · 14 Sep 2007 at 12:43

/edit - dp

div0 · 14 Sep 2007 at 16:09

My first thought was to consider using the ISNUMBER() feature. Then you can use If statements to check up/down 'x' number of rows from where you find a number.

Hope that helps.

Tomsk · 14 Sep 2007 at 16:29

Assuming that A1 is the top left cell then

=INDEX(INDIRECT("G" & MATCH(1,A1:A22,0) & ":G22"),MATCH("INVOICES",INDIRECT("A" & MATCH(1,A1:A22,0) & ":A22"),0),1)

returns the value you want.

Whoop · 15 Sep 2007 at 14:39

Haven't yet had chance to try this but definately will do and wanted to say thanks for all those that posted helping me, this is such a great forum

Whoop

Whoop · 17 Sep 2007 at 12:39

Nearly there, one last question

How do I get it to reference a different sheet in the workbook

Thanks,

Whoop

Tomsk · 17 Sep 2007 at 14:12

=Sheet1!A1 - for cell A1 in sheet 1, same workbook

=[Book2]Sheet1!$A$1 - for cell A1 in sheet 1 in workbook Book2. the 'Book2' might need changing to show more of the path, eg [C:\Book2]

Participant · 17 Sep 2007 at 14:46

Didn't know about that function. Match & index rocks! Good work!