result after referring to another sheet

[calisto]

I'm using Excel 16 -- I have an excel sheet ( sheet 2 ) with 2 columns used
ColA date
ColB long list of rising numbers ( years of )

what I'd like to do
on sheet 1, place a number in Col A say 220.65
add 1.5 to that -== 222.15

then lookup the sheet 2 database to find when the date that the number 222.15 ( min) occurs

print out the date on sheet 1 , say in Col B

I'm suggesting that the results be shown on a separate sheet because the requested number list will be
smaller but being added to regularly

is this possible ?

copy xls attached

thanks

[AliGW]

Do you mean Excel 2016?

[AliGW]

Your calculations are not quite right, I think: 246.81 plus 1.5 percent is 250.51.

However, try this in C5 copied down:

=A5*1.015

or maybe you mean this:

=A5*1.005

and this in E5 copied down:

=INDEX('Sheet 2 '!$A$2:$A$19,MATCH('Sheet 1'!C5,'Sheet 2 '!$B$2:$B$19,1))

[calisto]

hi, sorry the calcs are correct -- it shows " add 1.5" a number
not %
using the sheet 1 as attached
A5 + 1.5 = C5 =249.31 -- that part not a problem

then I need C5 to refer to sheet 2, col B to find when C5
becomes => than a number in ColB
this occurs at sheet 2, A16 which is greater than 249.31 so then
print the answer on sheet 1, E5 as 16-2-19

when I copied the formula into sheet1 , E5, it showed as NA

I don't understand the formula but just in case--
C5 must be => , not Match

thanks

[AliGW]

I don't understand the formula but just in case--
C5 must be => , not Match

Correct - you don't understand the MATCH function!!!

A match can be either exact or approximate, and I was using the latter. However, I am not sure about your logic: the next highest on the lookup sheet is 248.60, so why isn't the first required result 13.01.2019?

Please let us know - you may have to explain your logic in more detail.

[calisto]

Correct - you don't understand the MATCH function!!!

A match can be either exact or approximate, and I was using the latter. However, I am not sure about your logic: the next highest on the lookup sheet is 248.60, so why isn't the first required result 13.01.2019?

Please let us know - you may have to explain your logic in more detail.

hello AliGW, sorry to mislead you
you are correct, the first match is 13-1-19
I made a simple math error on C3 & caused confusion-- should be 248.31
the corrected sheet attached

[calisto]

Correct - you don't understand the MATCH function!!!

A match can be either exact or approximate, and I was using the latter. However, I am not sure about your logic: the next highest on the lookup sheet is 248.60, so why isn't the first required result 13.01.2019?

Please let us know - you may have to explain your logic in more detail.

the revised Excel sheet below shows the logic clearly on sheet 1
I tried the formula shown but couldn't get ti to work
when I copy/pasted, it wanted to know origin/destination formatting
tried both- same result-- wanted to save it
we have the final number in Col C5
what we need is to look up Sheet 2 for a match & then print the date

[Sam Capricci]

first, the value in A5 is 246.81, if you add 1.5 to that it is 248.31, not 249.31, you seem to have that noted in E3 so I'm not sure what the value in C5 represents.
BUT if you use this array formula to return the date you indicate it should return the date.

{=INDEX('Sheet 2 '!$A$2:$A$19,MATCH(TRUE,'Sheet 2 '!$B$2:$B$19>'Sheet 1'!C5,0))}

Activate it using ctrl+shift+enter in the formula bar so the braces {} appear on each side of the formula, you cannot add them yourself.

[calisto]

first, the value in A5 is 246.81, if you add 1.5 to that it is 248.31, not 249.31, you seem to have that noted in E3 so I'm not sure what the value in C5 represents.
BUT if you use this array formula to return the date you indicate it should return the date.

{=INDEX('Sheet 2 '!$A$2:$A$19,MATCH(TRUE,'Sheet 2 '!$B$2:$B$19>'Sheet 1'!C5,0))}

Activate it using ctrl+shift+enter in the formula bar so the braces {} appear on each side of the formula, you cannot add them yourself.

Hello Sambo kid, you were correct on the math q sorry
thanks but I haven't a clue how to use the formula
I only know basic stuff
I added it to C5 & after to E5 but the braces were already on it
copied the formula & pasted with Source formatting

could you explain in more detail how to use it please
thanks

[Sam Capricci]

enter it this way in the cell you want... =INDEX('Sheet 2 '!$A$2:$A$19,MATCH(TRUE,'Sheet 2 '!$B$2:$B$19>'Sheet 1'!C5,0))
then while your cursor is in the formula bar (beside the fx at the top) click inside the formula then hit ctrl+shift+enter all at the same time and the braces {} should appear around the formula. If not, try again. Once they appear they will "activate" the array then you can simply drag down or double click on the bottom right of the cell where it becomes a little square and it will autopopulate down the column.

[calisto]

thanks
i'll try to get it to work

[calisto]

enter it this way in the cell you want... =INDEX('Sheet 2 '!$A$2:$A$19,MATCH(TRUE,'Sheet 2 '!$B$2:$B$19>'Sheet 1'!C5,0))
then while your cursor is in the formula bar (beside the fx at the top) click inside the formula then hit ctrl+shift+enter all at the same time and the braces {} should appear around the formula. If not, try again. Once they appear they will "activate" the array then you can simply drag down or double click on the bottom right of the cell where it becomes a little square and it will autopopulate down the column.

sorry Sambo kid, I can't get it to work
when I copy/paste the formula into my sheet -- asks what type of formatting I need & wants to save it
never saw the braces
for me, it should not only do the job but easy to use
thanks anyway

[AliGW]

Please attach a sample workbook where you can't get it to work.

Try copying and pasting this:

=INDEX('Sheet 2 '!$A$2:$A$19,MATCH(TRUE,'Sheet 2 '!$B$2:$B$19>'Sheet 1'!C5,0))

Commit by pressing CTRL+SHIFT+ENTER (not just ENTER), and then drag copy down.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
3			sheet 1 ColA = 246.81		add 1.5 -- 248.31
4		( plus 1.5)				Sam's formula:
5	246.81		248.31		16/01/2019	13.01.2019
6	247.49		248.99		17/01/2019	15.01.2019

Sheet: Sheet 1

[Sam Capricci]

here is your's back with the formula in it

[Sam Capricci]

for me, it should not only do the job but easy to use

Not to be insulting but sometimes requests made do not lead to easy to use formulas.

[AliGW]

Sam's formula works - please see posts #12 and #13. Try copying and pasting from my post, please.

I understand why you are getting frustrated, however it is because you are trying to use a new type of formula. Once you master how to enter the formula, you will be fine. If you are still struggling after looking at those posts, please attach a fresh workbook in which you cannot make the formula work.

[Sam Capricci]

trying one more time with attachments instead...

[calisto]

trying one more time with attachments instead...

Sambo kid -- sorry for being a clutz
your instructions are fine but after #3, cell I5 shows still #N/A and not the date

as per the 3-1-19 sheet attached
the formula worked fine on cell C5 & C6 on your sheet
but when I grabbed the square and dragged it down to F10, it ceased working for cell C 7-10 -- weird
something i'm missing?

[calisto]

first I'd like to thank you both , AliGW & Sambo kid, for the help so far
at the moment I'm overwhelmed with other pressing matters which I must attend to
but i'll make time on W/E for this & get back latest Monday

thanking you

[AliGW]

You didn't extend the ranges to match the lookup data:

=INDEX('Sheet 2 '!$A$2:$A$24,MATCH(TRUE,'Sheet 2 '!$B$2:$B$24>'Sheet 1'!C5,0))

Excel 365 (Windows) 32 bit

	A	B	C	D	E	F
3			sheet 1 ColA = 246.81		add 1.5 -- 248.31
4		( plus 1.5)
5	246.81		248.31		16/01/2019	13.01.2019
6	247.49		248.99		17/01/2019	15.01.2019
7	248.89		250.39			19.01.2019
8	249.04		250.54		etc	20.01.2019
9	249.26		250.76			21.01.2019
10	249.72		251.22			23.10.2019

Sheet: Sheet 1

[calisto]

thanks AliGW, I had no idea that I could/should modify the formula


all good

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.