How to do vlookup on table where value is instrumental? See details.

[dmadhup]

Hi,

I want to do VLOOKUP on sheet1 to Sheet2 and get Price. But I want to see the result as show in sheet1. Saying that, VLOOKUP will pick the data where there is incremental.
This formula doesn't meet my requirements.
=VLOOKUP(A3,Sheet2!A:B,2,FALSE)

Any idea to achieve the goal?

 

[dmadhup]

Actually I want to get char from sheet2 when transition from
1 to 2 for level 1
2 to 3 for level 2
3 to 4 for level 3

like that....

 

[Peter_SSs]

Actually I want to get char from sheet2 when transition from
1 to 2 for level 1
2 to 3 for level 2
3 to 4 for level 3

like that....

I don't understand that. Could we have sample data and expected results with XL2BB?

 

[dmadhup]

Hi Peter,
I am trying to use XL2BB.
Meanwhile, i am trying to elaborate it.

here in sheet1 the green color M should be D. Because in Sheet2 D is the position where level goes from 1 to 2

 

[dmadhup]

SHEET1.xlsx
	A	B
7	Level	Char
8	0	A
9	1	B
10	1	C
11	1	D
12	2	E
13	2	F
14	2	G
15	2	H
16	2	I
17	3	J
18	3	K
19	4	L
20	1	M
21	1	M
22	1	M
Sheet2

 

[dmadhup]

SHEET1.xlsx
	A	B	C
1	Level	Char
2	0	A
3	1	M	Should D here at B3 cell
4	2	I
5	3	K
6
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=LOOKUP(REPT("Z",255),FILTER(Sheet2!B$2:B$20,Sheet2!A$2:A$20=A2))

 

[dmadhup]

Here is the sample result

SHEET1.xlsx
	A	B	C
1	Level	Char
2	0	A
3	1	D
4	2	I
5	3	K
6
Sheet1

Cell Formulas
Range	Formula
B2,B4:B5	B2	=LOOKUP(REPT("Z",255),FILTER(Sheet2!B$2:B$20,Sheet2!A$2:A$20=A2))

 

[Peter_SSs]

Thanks for getting XL2BB going.

Looking at post 14: This is different to what you had before where all the values in Sheet2 were ascending down the page.
In Sheet2, is there or will there ever be more data below row 22 as shown?
What I am getting at is I can see that 1 changes to 2 from row 11 to 12 but 1 appears again in rows 20:22. Suppose row 23 is 2 then we have 1 changing to 2 at rows 11/12 and rows 22/23.
In that case, how would we determine what to return in Sheet1?

 

[dmadhup]

Hi Peter,
Thank you for response.
Yes, I added more rows to test because there could be more 1s and 2s too. But we pick the first appearance of transition from 1 to 2 or 2 to 3.

 

[Peter_SSs]

we pick the first appearance of transition from 1 to 2 or 2 to 3.

OK, see if this is better

dmadhup 2020-08-07 1.xlsm
	A	B
1	Level	Price
2	0	A
3	1	B
4	1	C
5	1	D
6	2	E
7	2	F
8	2	G
9	2	H
10	2	I
11	3	J
12	3	K
13	4	L
14	1	M
15	1	M
16	1	M
17	2	N
18	3	O
19	3	O
20	3	O
21	3	O
22	4	P
23	1	Q
24
Sheet2

dmadhup 2020-08-07 1.xlsm
	A	B
1
2	0	A
3	1	D
4	2	I
5	3	K
6	4	L
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=INDEX(FILTER(Sheet2!B$2:B$30,(Sheet2!A$2:A$30=A2)*(Sheet2!A$3:A$31<>A2)),1)

 

[dmadhup]

Thank you Peter.
It works what I was expecting...