Need Help with multiple criteria labelled data

[clueless_excel]

So my data is labelled A1-5, from A-Z. I want to add labels into another column based of these i.e., A,B,C,D are 3, E,F,G,H are 20, I and J are 10 for example. Any advice on the best way to do this? I've tried to do a bunch of nested IFs but I reach the 64 limit before it covers all the possibilities.

This is how the data looks, I want to enter specific labels for start and end point based off the vial number but am unsure of the best way to solve this. Any advice?

 

[etaf]

lookup table
A to Z

= index( cell to bring back, Match(left(cell,1),range with alphabet , 0 ))

Book6
	A	B	C	D	E	F	G	H	I
1
2	a1		3					a	3
3	a2		3					b	3
4	a3		3					c	3
5	f1		20					d	3
6	f2		20					e	3
7	f3		20					f	20
8	f4		20					g	20
9	I1		10					h	20
10	I2		10					i	10
11	I3		10					j	10
12								k	10
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=INDEX($I$2:$I$12,MATCH(LEFT(A2,1),$H$2:$H$12,0))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[clueless_excel]

lookup table
A to Z

= index( cell to bring back, Match(left(cell,1),range with alphabet , 0 ))

Book6
	A	B	C	D	E	F	G	H	I
1
2	a1		3					a	3
3	a2		3					b	3
4	a3		3					c	3
5	f1		20					d	3
6	f2		20					e	3
7	f3		20					f	20
8	f4		20					g	20
9	I1		10					h	20
10	I2		10					i	10
11	I3		10					j	10
12								k	10
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=INDEX($I$2:$I$12,MATCH(LEFT(A2,1),$H$2:$H$12,0))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Thank you I appreciate it, didn't think to try INDEX. Looks like its working now

 

[etaf]

you are welcome

i'm sure others may answer with better solutions then a table - maybe choose , or something similar

 

[clueless_excel]

you are welcome

i'm sure others may answer with better solutions then a table - maybe choose , or something similar

Thanks. Honestly if it works it'll be good enough for the moment haha. Just a follow up question, how are you able to have it only look for the letters specifically. I'm relatively new to coding so its a bit confusing to understand haha

 

[etaf]

the value it is looking up - is
left(cell,1)
so that takes the first character in the cell
which in your case should be the alphabet

it is then using that to find that letter within the list A to Z - which i assumed you have filled in , in the table column H
once matches then it knows what row to lookin - and the index looks in that row in the column H and returns the value from column I

you could have used an vlookup() - i just always use index/match - as you can also specify columns to the left of the lookup - not possible with vlookup in its default use - there is a way - but not as written

have a read here - for explanation of index/match

How to use INDEX and MATCH

INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you...

exceljet.net

if you goto MREXCEL youtube site

https://www.youtube.com/@MrXL/videos

there are a few videos on the subject

 

[clueless_excel]

the value it is looking up - is
left(cell,1)
so that takes the first character in the cell
which in your case should be the alphabet

it is then using that to find that letter within the list A to Z - which i assumed you have filled in , in the table column H
once matches then it knows what row to lookin - and the index looks in that row in the column H and returns the value from column I

you could have used an vlookup() - i just always use index/match - as you can also specify columns to the left of the lookup - not possible with vlookup in its default use - there is a way - but not as written

have a read here - for explanation of index/match

How to use INDEX and MATCH

INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you...

exceljet.net

if you goto MREXCEL youtube site

https://www.youtube.com/@MrXL/videos

there are a few videos on the subject

Ah right thank you, appreciate the explanation and the literature. Useful stuff to know haha

 

[etaf]

you are welcome