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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,025
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,210
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,210
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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,150
Messages
5,835,685
Members
430,376
Latest member
eeehhhyyy

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