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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Hi There
Book2
ABCDEFG
112345
2JanFebMarAprMay
3500300600400450
4
5
6500Jan
75001
8
9
Sheet1


HTH


Dave
 

Panayiotis

New Member
Joined
Mar 22, 2005
Messages
38
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Panayiotis

New Member
Joined
Mar 22, 2005
Messages
38

ADVERTISEMENT

aladin, yes just one number and there are no numbers to follow past the 3rd row.
 

Panayiotis

New Member
Joined
Mar 22, 2005
Messages
38
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

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
Top