Index, Match or vlookup?

Panayiotis

New Member
Joined
Mar 22, 2005
Messages
38
My layout is such:
column A is blank
rows B1:E1 just have numbers 1,2,3,4
rows b2:E2 have dates jan, feb, mar, apr
rows b3:e3 1st 3 cells are blank, then e3 has a number 500

in a3 i want a formula as such that reads all cells in the same row, in this case be:e3 blanks and populated and as soon as it comes aross a number (500 in this case) it gives me the number directly above the date in this case the number 4 in cell e1.

thanks
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi There
Book2
ABCDEFG
112345
2JanFebMarAprMay
3500300600400450
4
5
6500Jan
75001
8
9
Sheet1


HTH


Dave
 
Upvote 0
thanks dave.

what if i had column A blank and leave that for my formula
and i only had one number

in other words what i am trying to say is look in all the cells to the right and as soon as you come across a populated cell with a number go get me the number that is 2 rows above that.

1 2 3 4
1/1/2007 2/1/2007 3/1/2007 4/1/2007
formula here 500
 
Upvote 0
thanks dave.

what if i had column A blank and leave that for my formula
and i only had one number

in other words what i am trying to say is look in all the cells to the right and as soon as you come across a populated cell with a number go get me the number that is 2 rows above that.

1 2 3 4
1/1/2007 2/1/2007 3/1/2007 4/1/2007
formula here 500

Do the rows from the 3rd row on always house just one number?
 
Upvote 0
also, it could be any number, i just picked 500 as an example. all i am trying to tell the formula is look at the entire row and as soon as you come aross a number go get me the number that is 2 rows directly above it.
 
Upvote 0
also, it could be any number, i just picked 500 as an example. all i am trying to tell the formula is look at the entire row and as soon as you come aross a number go get me the number that is 2 rows directly above it.

If the 3rd houses just one number at most:

=LOOKUP(9.99999999999999E+307,B3:E3,B1:E1)

If the 3rd can houses any number of numeric items...

Control+shift+enter:

=INDEX(B1:E1,MATCH(TRUE,ISNUMBER(B3:E3),0))
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top