Nested If Statement

[igmike]

I have attempted several functions Vlookup+IF, index/match. To complete step 1 in manipulating the given data for my purposes.

Sample data:


I no longer have the program that allows you to post excel data in a neat format to a forum post, nor could I find a reference in search. If someone could let me know, I can post data and desired solution.

Thanks in advance.

 

[igmike]

I've found the program, Excel Jeanie, though cannot find a working download link, it appears the link from excel-jeanie is broken.

 

[jtakw]

Hi,

Check post #2 here: https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[igmike]

Thanks jtakw, I found an older version of Excel Jeanie.

My goal is to have the formula lookup in column B, the number next to VGA (possibilities include 100, 95+, 95, 90+, 90, 85+, and 85). From there it would take the sold price in column I, and place it in the appropriate column M-S.

Aside from Vlookup, Index/Match, I had also tried MID, Right, etc, however the placement of the number differ in columns. Help in the right direction would be appreciated.

Step 2, will be able to generate other prices for the specific data in columns M-S for missing sold prices based on several factors. (previous date sold) etc.



price-guide

	B	C	D	E	F	G	H	I
2	Title	Platform	Grade	Format	Bids	Average Sale Price	Items Sold	Total Sales
3	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 85+ NM+Item ID: 272826824787Seller: 5***v	Playstation 3	VGA 85+	Fixed Price	0	$899.99	1	$899.99
4	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 85+ NM+Item ID: 272839937690Seller: 5***v	Playstation 3	VGA 85+	Fixed Price	0	$899.99	1	$899.99
5	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 85+ NM+Item ID: 272888440351Seller: 5***v	Playstation 3	VGA 85+	Fixed Price	0	$899.99	1	$899.99
6	Red Dead Redemption SPECIAL EDITION, New Sealed! Sony PS3 VGA 90+Item ID: 201778548898Seller: e***e	Playstation 3	VGA 90+	Store Fixed Price	0	$749.99	1	$749.99

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

price-guide

	M	N	O	P	Q	R	S
1		VGA Grade
2	100	95+	95	90+	90	85+	85
3
4
5
6

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[igmike]

lost in the mix. any pointers?

 

[jtakw]

Hi again igmike,

Is there a reason you can not use the values in Column D (Grade) for the Lookup?
Also, if we're looking up Only the VGA grade (100, 95+, 90, etc.), and since you have multiple lines with the same Grade description, are the Prices you're looking for in Column "I" Always the same for the same "Grade"?

 

[igmike]

Hi there Jt,

the data found in coulmn D does not exist with what I am given, I populated it trying MID formulas, however it would be very difficult to normalize the text. Thus yielding diff incorrect data.

I posted an array with index match isnumber search though I cant get excel 13 to let me enter to see if it will work.

Column A has different variables, games, sold dates and platforms, so only for that row will the corrospondeing grade be appropriate.

Further steps need to be taken into account once step 1 is completed.

Ill post here what ive come up with so far.

 

[jtakw]

May be this can help get you started.

You haven't answered my question regarding if given the Same Grade, will the Price Always be the Same, I'm assuming it is.

Book1
	B	C	D	E	F	G	H	I
2	Title	Platform	Grade	Format	Bids	Average Sale Price	Items Sold	Total Sales
3	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 85+ NM+Item ID: 272826824787Seller: 5***v	Playstation 3	85+	Fixed Price	0	$899.99	1	$899.99
4	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 85+ NM+Item ID: 272839937690Seller: 5***v	Playstation 3	85+	Fixed Price	0	$899.99	1	$899.99
5	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 85+ NM+Item ID: 272888440351Seller: 5***v	Playstation 3	85+	Fixed Price	0	$899.99	1	$899.99
6	Red Dead Redemption SPECIAL EDITION, New Sealed! Sony PS3 VGA 90+Item ID: 201778548898Seller: e***e	Playstation 3	90+	Store Fixed Price	0	$749.99	1	$749.99
7	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 90 NM+Item ID: 272888440351Seller: 5***v		90					$859.99
8	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 90+NM+Item ID: 272888440351Seller: 5***v		90+					$749.99
9	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 100 NM+Item ID: 272888440351Seller: 5***v		100					$999.99
Sheet53

Cell Formulas
Range	Formula
D3	=TRIM(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(MID(B3,SEARCH("VGA",B3)+3,255),"+","+ "))," ",REPT(" ",LEN(B3))),LEN(B3)))

D3 formula copied down to extract the VGA Grade as a Helper Column.

Book1
	M	N	O	P	Q	R	S
1		VGA Grade
2	100	95+	95	90+	90	85+	85
3	999.99			749.99	859.99	899.99
Sheet53

Cell Formulas
Range	Formula
M3	=IFERROR(VLOOKUP(TEXT(M2,"0"),$D3:$I9,6,0),"")

M3 formula copied across using Helper Column D.

 

[igmike]

@jt

That looks good. was late/early when I read your response, the answer to your question is no, one of the same grade can and likely will sell at diff prices. Also, There are a few grades I failed to insert. U100, U95+, U95, U90, and 80+.

Then is step 3. I have yet to come up with the forumla to use, but it would involve date from last sold, and a few other factors. to determine prices of grades not provided.

Thanks again.

 

[jtakw]

Thanks for replying.

Well, seems like it would be difficult to help until you have something setup...

Need various samples of the Column B data with different "Grades" to try to extract.
If the same "Grade" is sold at different prices, "which" should be the one to report on the second table?

Sounds like you've got a few things to work out...........