Question description:
This user has given permission to use the problem statement for this
blog.
I need to create a formula that fills finds the Sheet (col A) and then the Component (col B) and then adds the value I put in Col H into a cell in the corresponding cell of column of the found sheet.
Solved by Z. S. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
22/08/2018 - 06:04
Hi welcome!
User
22/08/2018 - 06:04
hello
Excelchat Expert
22/08/2018 - 06:04
Do you have a sheet to upload?
User
22/08/2018 - 06:05
uploading
[Uploaded an Excel file]
User
22/08/2018 - 06:06
allow me to recap what i need
Excelchat Expert
22/08/2018 - 06:06
Ok, I have sheet open
Excelchat Expert
22/08/2018 - 06:06
Yes, pelase
User
22/08/2018 - 06:06
its a little different than what i stated in the initial question.
User
22/08/2018 - 06:09
i need to look for be able to enter a value in COL A of the LOOKUP TAB and the formula search for the value in COL B and update a cell in COL I where that value was found
User
22/08/2018 - 06:09
FOR EXAMPLE...
User
22/08/2018 - 06:09
starting in the lookup tab sheet
User
22/08/2018 - 06:10
we see row 2
User
22/08/2018 - 06:10
15382100
User
22/08/2018 - 06:10
component "595-607"
Excelchat Expert
22/08/2018 - 06:10
Yes the first 15382100
User
22/08/2018 - 06:11
i want column h to search for column b and when it finds the value in other sheets... it will update the price (column I )
User
22/08/2018 - 06:11
with what i placed in column H
Excelchat Expert
22/08/2018 - 06:12
So you put a price in COLUMN H and lookup col B?
Excelchat Expert
22/08/2018 - 06:13
Or you put a component in col H?
User
22/08/2018 - 06:13
first one
Excelchat Expert
22/08/2018 - 06:13
Also col I is hidden?
Excelchat Expert
22/08/2018 - 06:13
Ok, a price in COL H, in the LOOKUP sheet
User
22/08/2018 - 06:14
i put a price in column H and it looks in other sheets for the VALUE in COL B and replaces prices in other sheets in COL I when that component is found
Excelchat Expert
22/08/2018 - 06:14
Which other sheets?
Excelchat Expert
22/08/2018 - 06:15
the sheet listed in COL A?
User
22/08/2018 - 06:15
correct
Excelchat Expert
22/08/2018 - 06:16
There are 3 cost columns in sheets
Excelchat Expert
22/08/2018 - 06:16
which one?
Excelchat Expert
22/08/2018 - 06:18
Also, are lookups exact or near. Near will find closest rate
Excelchat Expert
22/08/2018 - 06:20
?? where is the cost in the sheets? H3? or under current total?
User
22/08/2018 - 06:20
one moment
User
22/08/2018 - 06:20
lookups are exact
User
22/08/2018 - 06:21
price in column I
Excelchat Expert
22/08/2018 - 06:22
Col I , is same as K3?
Excelchat Expert
22/08/2018 - 06:22
Or is that just for ALUM?
User
22/08/2018 - 06:24
youre right
User
22/08/2018 - 06:24
hmmm
User
22/08/2018 - 06:24
lets use col H instead
Excelchat Expert
22/08/2018 - 06:24
ok
Excelchat Expert
22/08/2018 - 06:25
So let me restate the problem statement
Excelchat Expert
22/08/2018 - 06:25
LOOKUP table- enter price in H
Excelchat Expert
22/08/2018 - 06:26
For the TAB = COL A, find the price for component COL B
Excelchat Expert
22/08/2018 - 06:27
That price (the formula) goes in col I of the LOOKUP table (currently hiidden)
Excelchat Expert
22/08/2018 - 06:27
correct?
User
22/08/2018 - 06:28
thats sounds right
Excelchat Expert
22/08/2018 - 06:28
Price is EXACT, so if not found , enter ZERO , OR NOT FOUND
User
22/08/2018 - 06:28
lets do NOT FOUND
Excelchat Expert
22/08/2018 - 06:28
OK
Excelchat Expert
22/08/2018 - 06:29
Let me get the formula in for the first sheet. Then we should have enough time to review.
Excelchat Expert
22/08/2018 - 06:29
Please stand by
User
22/08/2018 - 06:30
i'll be patient
User
22/08/2018 - 06:30
thanks for the heko
User
22/08/2018 - 06:30
help*
Excelchat Expert
22/08/2018 - 06:39
This file is huge
User
22/08/2018 - 06:39
it is quite large
User
22/08/2018 - 06:39
lol
Excelchat Expert
22/08/2018 - 06:39
It may be easier to give you the example and the formula and we review
Excelchat Expert
22/08/2018 - 06:40
On your sheet enter 2,75 in cell H4
User
22/08/2018 - 06:40
ok
Excelchat Expert
22/08/2018 - 06:41
=INDEX('15382100'!B15:I26,MATCH( 'LOOKUP TAB'!H4,'15382100'!H15:H26,0),7)
Excelchat Expert
22/08/2018 - 06:41
Put this formula in i4
User
22/08/2018 - 06:41
ok one moment
User
22/08/2018 - 06:43
my computer is freezing up... gimme a min
User
22/08/2018 - 06:43
well... excel is anyway
Excelchat Expert
22/08/2018 - 06:43
Yes, it is quite large
User
22/08/2018 - 06:46
ok
User
22/08/2018 - 06:46
in sheet > enter h4>
User
22/08/2018 - 06:46
shet ?*
User
22/08/2018 - 06:46
sheet**
Excelchat Expert
22/08/2018 - 06:46
enter 2.75 -test number
Excelchat Expert
22/08/2018 - 06:47
=INDEX('15382100'!B15:I26,MATCH( 'LOOKUP TAB'!H4,'15382100'!H15:H26,0),7)
Excelchat Expert
22/08/2018 - 06:47
put that in i4
Excelchat Expert
22/08/2018 - 06:47
This is just to see if this is what you wanted
User
22/08/2018 - 06:49
so i entered "9999" as a test number into H4 of the LOOKUP TAB sheet
User
22/08/2018 - 06:49
in the same sheet i also entered your formula.
User
22/08/2018 - 06:50
into i4
Excelchat Expert
22/08/2018 - 06:50
We want to test the exact match first , but OK
User
22/08/2018 - 06:50
is that right or did you mean something else?
User
22/08/2018 - 06:51
i wish there was video chat haha
Excelchat Expert
22/08/2018 - 06:51
Put 2.75 in H4
User
22/08/2018 - 06:51
ok
User
22/08/2018 - 06:51
done
Excelchat Expert
22/08/2018 - 06:51
Should see 2.75 in i4
User
22/08/2018 - 06:51
correct
Excelchat Expert
22/08/2018 - 06:51
OK, test 1 is a success
Excelchat Expert
22/08/2018 - 06:52
Now change H4 to 2.70 and you should get an ERROR msg
User
22/08/2018 - 06:52
right
Excelchat Expert
22/08/2018 - 06:52
=IFERROR(INDEX('15382100'!B15:I26,MATCH( 'LOOKUP TAB'!H4,'15382100'!H15:H26,0),7),"Not Found")
Excelchat Expert
22/08/2018 - 06:53
Put that revised formula in i4 and that will generate a NOT FOUND message
User
22/08/2018 - 06:54
correct
Excelchat Expert
22/08/2018 - 06:54
Ok , that is the formula
User
22/08/2018 - 06:55
uhh..
User
22/08/2018 - 06:55
oh... one sec...
User
22/08/2018 - 06:55
lemme apply this to what i need.
Excelchat Expert
22/08/2018 - 06:56
This current formula only works with tab = 15382100
Excelchat Expert
22/08/2018 - 06:56
Problem is the 750 tabs you have
Excelchat Expert
22/08/2018 - 06:58
And also noticed you have more rates lower in the sheet?
User
22/08/2018 - 06:59
[Uploaded an Excel file]
Excelchat Expert
22/08/2018 - 06:59
Not sure what you tried to upload-got an error?
User
22/08/2018 - 07:00
one moment
User
22/08/2018 - 07:00
please goto sheet 15382100
Excelchat Expert
22/08/2018 - 07:00
ok
Excelchat Expert
22/08/2018 - 07:01
Im there
User
22/08/2018 - 07:01
i need cell H22 to update because "530-4421" was found in B22
User
22/08/2018 - 07:02
so whenever i enter a value in H4 of the LOOKUP TAB...
Excelchat Expert
22/08/2018 - 07:02
yes, you entered 2.75 and found
User
22/08/2018 - 07:03
it updates on all tabs that find that "530-4421"
Excelchat Expert
22/08/2018 - 07:03
b22= 590-4421
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
